CROSS TAB SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CROSS TAB SQL

Author

Topic

dockies
Starting Member

25 Posts

Posted - 2010-02-10 : 07:45:26

Hi,

I received a request from my clients that i need to make a cross tab report out of this record.

I created 2 tables one that handles the columns and the other one is for the rows:

1st table (tbl_fields) (columns)
ID-----FieldName-----Department
1---------Name---------AB
2---------Age----------AB
3---------email--------AB
4---------gender-------AB
5---------location-----AB

2nd Table (tbl_rows) (Rows)
ID-----tbl_field_id-----FieldValue
1----------1---------------Cray
2----------2---------------25
3----------3---------------craygmail.com
4----------4---------------male
5----------5---------------canada
6----------1---------------Carlo
7----------2---------------28
8----------3---------------carlo.otaneg@gss.com
9----------4---------------male
10---------5---------------hawaii

I need a result like this one:

Name-----Age-----Email---------------------Gender---Location
Cray ---25 -----craygmail.com-----------------Male----Canada
Carlo ---28 -----carlo.otaneg@gss.com----------Male----Hawaii

What i did so far is i created a temp table that will handle the columns based in my Column table and i will update the columns to insert the appropriate rows based on their tbl_field_id.

the problem the processing takes too long even if i was just loading the top 50 records of it. i think the process was so big especially because the fields that needs to be converted to columns are usually 20 rows or more.

can anyone help me with this... is there a way that i can speed up the process?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 08:24:56

see

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-10 : 08:33:01

Your data is not in a good format.

Ideally you'd want to get the data like this:


[personId] | [field] | [value]
-----------+---------+-------------
1          | 1       | Cray
1          | 2       | 25
1          | 3       | craygmail.com
1          | 4       | male
1          | 5       | Canada
2          | 1       | Carlo
2          | 2       | 28
....

Basically you need a column that associates the various fields so you know which person each field belongs to.

Here there is a [personId] field so you know that all the fields for [personId] 1 all belong to the same person.

THEN you can pivot the rows very quickly and efficiently either by using 2005's PIVOT function or by writing a dynamic cross tab report.

At the moment the format you have your data doesn't let you do that as you can't (set based) work out which person each field belongs to.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dockies
Starting Member

25 Posts

Posted - 2010-02-10 : 09:01:04

Ok this is a clearer version of my problem.

As you can see the look up id that can be use to connect the columns and rows table is the tbl_field_id.

Also the columns are not fixed to 5 sometimes different department has more columns than the others so it has to be dynamic.

Like for my example the yellow ones are the AB department and the blue one is the IT which has more columns than the AB dept.

the tbl_field_id corresponds to the columns so when
tbl_values has tbl_field_id 1 and field_value Cray it should look like this:

Name = tbl_field_id 1
Cray = Value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 09:29:31

Here's a mock up of your situation with solution




SELECT * INTO tbl_fields 
FROM
(
SELECT 1 AS ID,'Name' AS FieldName,'AB' AS Department UNION ALL
SELECT 2,'Age','AB' UNION ALL
SELECT 3,'email','AB' UNION ALL
SELECT 4,'gender','AB' UNION ALL
SELECT 5,'location','AB' UNION ALL
SELECT 6,'Name','IT' UNION ALL 
SELECT 7,'Age','IT' UNION ALL
SELECT 8,'email','IT' UNION ALL
SELECT 9,'gender','IT' UNION ALL
SELECT 10,'location','IT' UNION ALL
SELECT 11,'phone','IT' UNION ALL
SELECT 12,'id number','IT' 
)t1



SELECT * INTO tbl_rows 
FROM
(
SELECT 1 AS ID,1 AS tbl_field_id,'Cray' AS FieldValue UNION ALL
SELECT 2,2,'25' UNION ALL
SELECT 3,3,'craygmail.com' UNION ALL
SELECT 4,4,'male' UNION ALL
SELECT 5,5,'canada' UNION ALL
SELECT 6,1,'Carlo' UNION ALL
SELECT 7,2,'28' UNION ALL
SELECT 8,3,'carlo.otaneg@gss.com' UNION ALL
SELECT 9,4,'male' UNION ALL
SELECT 10,5,'hawaii' UNION ALL
SELECT 11,6,'James' UNION ALL
SELECT 12,7,'28' UNION ALL
SELECT 13,8,'james_john@yahoo.com' UNION ALL
SELECT 14,9,'male' UNION ALL
SELECT 15,10,'canada' UNION ALL
SELECT 16,11,'02-784-4877' UNION ALL
SELECT 17,12,'1248' 
)t2

DECLARE @FieldNameList varchar(max),@Sql varchar(max),@department varchar(100)
SET @department='AB'

SELECT @FieldNameList= COALESCE(@FieldNameList+'],[','')+FieldName
FROM tbl_fields
WHERE Department= @department

SELECT @FieldNameList= '['+@FieldNameList+']'

SET @Sql='
SELECT '+ @FieldNameList +'
FROM
(
SELECT Seq,FieldValue,FieldName,Department
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY tbl_field_id ORDER BY ID) AS Seq,*
FROM tbl_rows
)r
INNER JOIN tbl_fields f
ON r.tbl_field_id=f.ID
WHERE Department = ''' + @department + '''
)t
PIVOT(MAX(FieldValue) FOR FieldName IN (' +  @FieldNameList + '))p'
EXEC(@SQL)

drop table tbl_fields

drop table tbl_rows


output
-------------------------------
for Department='AB'

Name                 Age                  email                gender               location
-------------------- -------------------- -------------------- -------------------- --------------------
Cray                 25                   craygmail.com        male                 canada
Carlo                28                   carlo.otaneg@gss.com male                 hawaii

For department='IT'

Name                 Age                  email                gender               location             phone                id number
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
James                28                   james_john@yahoo.com male                 canada               02-784-4877          1248

------------------------------------------------------------------------------------------------------
SQL Server MVP

dockies
Starting Member

25 Posts

Posted - 2010-02-11 : 08:10:44

Thanks visakh16 Works like a charm! :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 08:13:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dockies
Starting Member

25 Posts

Posted - 2010-02-12 : 01:05:19

Sorry visakh16 but i need you help again...

It turns out that the table structure was a little different than i first described it...

now we only have 1 table handling the rows and columns.

as seen in my example below:

Now this is what i did:

--select top 10 * from simpleform
DECLARE @FieldNameList nvarchar(4000),@Sql varchar(max),@URL varchar(100)
SET @URL='http://www.sample.url'

SELECT @FieldNameList= COALESCE(@FieldNameList+'],[','')+ a.field_name
FROM (select field_name from simpleform where URL like @URL and field_name <> '' group by field_name) a

SELECT @FieldNameList= '['+@FieldNameList+']'
PRINT @FieldNameList

SET @Sql='
SELECT '+ @FieldNameList +'
FROM
(
SELECT seq,f.field_name,cast(f.field_value as nvarchar(4000)) as Field_Value
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY field_name ORDER BY ID) AS Seq,ID,field_name
FROM simpleform
WHERE URL like '''+@URL+''' and field_name <> ''''
group by ID,field_name
)r
INNER JOIN simpleform f
ON r.ID = f.ID
WHERE URL like ''' + @URL + '''
)t
PIVOT(MAX(Field_Value) FOR Field_Name IN (' + @FieldNameList + '))p'

EXEC(@SQL)

That works fine displaying all the dynamic columns and their rows. but i also need to display the batch no. :( problem is when i display the batch no everything became so mess up. :( most of the time the records are being doubled :(

i wish i am that great with sql :(

could you help me again? thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:21:39

then how would you determine what all you want to view? where has department gone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dockies
Starting Member

25 Posts

Posted - 2010-02-12 : 01:35:16

oops i am very sorry visakh16 the department is in the table i forgot to include it.

There is a "department field" right after the "batch_no"

Thank you again...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:41:00

quote:
Originally posted by dockies

oops i am very sorry visakh16 the department is in the table i forgot to include it.

There is a "department field" right after the "batch_no"

Thank you again...

can you post your table data once in select format like i posted yesterday.

ie. like


SELECT * INTO tbl_rows 
FROM
(
SELECT 1 AS ID,1 AS tbl_field_id,'Cray' AS FieldValue UNION ALL
SELECT 2,2,'25' UNION ALL
SELECT 3,3,'craygmail.com' UNION ALL
SELECT 4,4,'male' UNION ALL
SELECT 5,5,'canada' UNION ALL
SELECT 6,1,'Carlo' UNION ALL
SELECT 7,2,'28' UNION ALL
SELECT 8,3,'carlo.otaneg@gss.com' UNION ALL
SELECT 9,4,'male' UNION ALL
SELECT 10,5,'hawaii' UNION ALL
SELECT 11,6,'James' UNION ALL
SELECT 12,7,'28' UNION ALL
SELECT 13,8,'james_john@yahoo.com' UNION ALL
SELECT 14,9,'male' UNION ALL
SELECT 15,10,'canada' UNION ALL
SELECT 16,11,'02-784-4877' UNION ALL
SELECT 17,12,'1248' 
)t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dockies
Starting Member

25 Posts

Posted - 2010-02-12 : 02:02:21

Im sorry for the late reply... sure here it is:

SELECT * INTO tbl_fields
FROM
(
SELECT 1 as ID,1 as Batch_No,'AB' as Department,'Name' as Field_Name,'Cray' as Field_Value UNION ALL
SELECT 2 as ID,1 as Batch_No,'AB' as Department,'Age' as Field_Name,'25' as Field_Value UNION ALL
SELECT 3 as ID,1 as Batch_No,'AB' as Department,'email' as Field_Name,'cray@gmail.com' as Field_Value UNION ALL
SELECT 4 as ID,1 as Batch_No,'AB' as Department,'gender' as Field_Name,'Male' as Field_Value UNION ALL
SELECT 5 as ID,1 as Batch_No,'AB' as Department,'location' as Field_Name,'Canada' as Field_Value UNION ALL
SELECT 6 as ID,2 as Batch_No,'AB' as Department,'Name' as Field_Name,'Carlo' as Field_Value UNION ALL
SELECT 7 as ID,2 as Batch_No,'AB' as Department,'Age' as Field_Name,'28' as Field_Value UNION ALL
SELECT 8 as ID,2 as Batch_No,'AB' as Department,'email' as Field_Name,'carlo.fox@gmail.com' as Field_Value UNION ALL
SELECT 9 as ID,2 as Batch_No,'AB' as Department,'gender' as Field_Name,'Male' as Field_Value UNION ALL
SELECT 10 as ID,2 as Batch_No,'AB' as Department,'location' as Field_Name,'Hawaii' as Field_Value UNION ALL
SELECT 11 as ID,3 as Batch_No,'IT' as Department,'Name' as Field_Name,'James' as Field_Value UNION ALL
SELECT 12 as ID,3 as Batch_No,'IT' as Department,'Age' as Field_Name,'28' as Field_Value UNION ALL
SELECT 13 as ID,3 as Batch_No,'IT' as Department,'email' as Field_Name,'james_john@yahoo.com' as Field_Value UNION ALL
SELECT 14 as ID,3 as Batch_No,'IT' as Department,'gender' as Field_Name,'male' as Field_Value UNION ALL
SELECT 15 as ID,3 as Batch_No,'IT' as Department,'location' as Field_Name,'canada' as Field_Value UNION ALL
SELECT 16 as ID,3 as Batch_No,'IT' as Department,'phone' as Field_Name,'02-784-4877' as Field_Value UNION ALL
SELECT 17 as ID,3 as Batch_No,'IT' as Department,'id number' as Field_Name,'1248' as Field_Value UNION ALL
SELECT 18 as ID,4 as Batch_No,'IT' as Department,'Name' as Field_Name,'Tina' as Field_Value UNION ALL
SELECT 19 as ID,4 as Batch_No,'IT' as Department,'Age' as Field_Name,'28' as Field_Value UNION ALL
SELECT 20 as ID,4 as Batch_No,'IT' as Department,'email' as Field_Name,'tina_mey@yahoo.com' as Field_Value UNION ALL
SELECT 21 as ID,4 as Batch_No,'IT' as Department,'gender' as Field_Name,'female' as Field_Value UNION ALL
SELECT 22 as ID,4 as Batch_No,'IT' as Department,'location' as Field_Name,'canada' as Field_Value UNION ALL
SELECT 23 as ID,4 as Batch_No,'IT' as Department,'phone' as Field_Name,'07-781-7777' as Field_Value UNION ALL
SELECT 24 as ID,4 as Batch_No,'IT' as Department,'id number' as Field_Name,'5874' as Field_Value
)t1

select * from tbl_fields
drop table tbl_fields

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 07:36:09

and here's your solution.


SELECT * INTO tbl_fields 
FROM
(
SELECT 1 as ID,1 as Batch_No,'AB' as Department,'Name' as Field_Name,'Cray' as Field_Value UNION ALL
SELECT 2 as ID,1 as Batch_No,'AB' as Department,'Age' as Field_Name,'25' as Field_Value UNION ALL
SELECT 3 as ID,1 as Batch_No,'AB' as Department,'email' as Field_Name,'cray@gmail.com' as Field_Value UNION ALL
SELECT 4 as ID,1 as Batch_No,'AB' as Department,'gender' as Field_Name,'Male' as Field_Value UNION ALL
SELECT 5 as ID,1 as Batch_No,'AB' as Department,'location' as Field_Name,'Canada' as Field_Value UNION ALL
SELECT 6 as ID,2 as Batch_No,'AB' as Department,'Name' as Field_Name,'Carlo' as Field_Value UNION ALL
SELECT 7 as ID,2 as Batch_No,'AB' as Department,'Age' as Field_Name,'28' as Field_Value UNION ALL
SELECT 8 as ID,2 as Batch_No,'AB' as Department,'email' as Field_Name,'carlo.fox@gmail.com' as Field_Value UNION ALL
SELECT 9 as ID,2 as Batch_No,'AB' as Department,'gender' as Field_Name,'Male' as Field_Value UNION ALL
SELECT 10 as ID,2 as Batch_No,'AB' as Department,'location' as Field_Name,'Hawaii' as Field_Value UNION ALL
SELECT 11 as ID,3 as Batch_No,'IT' as Department,'Name' as Field_Name,'James' as Field_Value UNION ALL
SELECT 12 as ID,3 as Batch_No,'IT' as Department,'Age' as Field_Name,'28' as Field_Value UNION ALL
SELECT 13 as ID,3 as Batch_No,'IT' as Department,'email' as Field_Name,'james_john@yahoo.com' as Field_Value UNION ALL
SELECT 14 as ID,3 as Batch_No,'IT' as Department,'gender' as Field_Name,'male' as Field_Value UNION ALL
SELECT 15 as ID,3 as Batch_No,'IT' as Department,'location' as Field_Name,'canada' as Field_Value UNION ALL
SELECT 16 as ID,3 as Batch_No,'IT' as Department,'phone' as Field_Name,'02-784-4877' as Field_Value UNION ALL
SELECT 17 as ID,3 as Batch_No,'IT' as Department,'id number' as Field_Name,'1248' as Field_Value UNION ALL
SELECT 18 as ID,4 as Batch_No,'IT' as Department,'Name' as Field_Name,'Tina' as Field_Value UNION ALL
SELECT 19 as ID,4 as Batch_No,'IT' as Department,'Age' as Field_Name,'28' as Field_Value UNION ALL
SELECT 20 as ID,4 as Batch_No,'IT' as Department,'email' as Field_Name,'tina_mey@yahoo.com' as Field_Value UNION ALL
SELECT 21 as ID,4 as Batch_No,'IT' as Department,'gender' as Field_Name,'female' as Field_Value UNION ALL
SELECT 22 as ID,4 as Batch_No,'IT' as Department,'location' as Field_Name,'canada' as Field_Value UNION ALL
SELECT 23 as ID,4 as Batch_No,'IT' as Department,'phone' as Field_Name,'07-781-7777' as Field_Value UNION ALL
SELECT 24 as ID,4 as Batch_No,'IT' as Department,'id number' as Field_Name,'5874' as Field_Value 
)t1



DECLARE @FieldNameList varchar(max),@Sql varchar(max),@department varchar(100)
SET @department='IT'

SELECT   @FieldNameList= '[' + LEFT(t.u,LEN(t.u)-2)
FROM (SELECT  Field_Name + '],[' FROM tbl_fields
WHERE Department= @department GROUP BY Field_Name ORDER BY MIN(ID) FOR XML PATH('') )t(u)



SET @Sql='
SELECT Batch_No,'+ @FieldNameList +'
FROM
(
SELECT Field_Value,Field_Name,Department,Batch_No
FROM
(
SELECT *
FROM tbl_fields
)r
WHERE Department = ''' + @department + '''
)t
PIVOT(MAX(Field_Value) FOR Field_Name IN (' +  @FieldNameList + '))p
ORDER BY Batch_No'
EXEC(@SQL)

drop table tbl_fields


output

for department 'AB'

Batch_No	Name	Age	email	gender	location
1	Cray	25	cray@gmail.com	Male	Canada
2	Carlo	28	carlo.fox@gmail.com	Male	Hawaii

for department 'IT'

Batch_No	Name	Age	email	gender	location	phone	id number
3	James	28	james_john@yahoo.com	male	canada	02-784-4877	1248
4	Tina	28	tina_mey@yahoo.com	female	canada	07-781-7777	5874

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dockies
Starting Member

25 Posts

Posted - 2010-02-12 : 08:24:24

Your example is working perfectly fine.... but when i changed the table name to the original table it says Msg 208, Level 16, State 1, Line 6
Invalid object name 'simpleform'. simpleform is the name of the table.

Why is that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:26:58

did you change it every where? can you show your used query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dockies
Starting Member

25 Posts

Posted - 2010-02-12 : 08:30:18

sorry my bad... i was disconnected to the server that why it cant find the table sorry about that...

ill check this right away.
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:31:09

ok check and let me know

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dockies
Starting Member

25 Posts

Posted - 2010-02-12 : 08:41:57

Wow man your the best! it sucks that i don't really understand how your code works:p but the thing is it works... damn i wish i can understand how did this code works hehe

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:45:16

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:46:04

for understanding pivot see here

http://msdn.microsoft.com/en-us/library/ms177410.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dockies
Starting Member

25 Posts

Posted - 2010-02-12 : 08:56:45

:) thanks... the thing that i dont understand quite well is this part:

SELECT @FieldNameList= '[' + LEFT(t.u,LEN(t.u)-2)
FROM (SELECT Field_Name + '],[' FROM tbl_fields
WHERE Department= @department GROUP BY Field_Name ORDER BY MIN(ID) FOR XML PATH('') )t(u)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources