Cross tab - 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 2008 Forums

Transact-SQL (2008)

Cross tab

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-05-28 : 10:44:36

Hi, I have the following scenario - for very column in the main_table there is a corressponding column column_a, column_b in another mapping table. This table is @Tbl as below. What I need to do is show all the columns of the main_table and add the addtional mapping columns alongside to this and show the entire resultset, so this would a cross tab view with columns from the main table as well.I have written the following code to accomplish this. The joining key in both tables is ID and in case ID is not available in the mapping table then it picks up the default values for ID=-1.

Now there may be close to 400 columns in main_table(please don't ask me why, this is the scenario, this is what the user needs to see.I don't have any option here).Is there a better, faster way to achieve what I am doing. You can imagine the columns to be 400*3=1200 in all in the final select/view etc., 400 of main table and another 400*2 in all to match column_a, column_b. Any advise?

DECLARE @MAIN_TABLE TABLE (ID INT PRIMARY KEY,
NAME VARCHAR(50),
AGE INT
)

--FOR EACH COLUMN IN MAIN_TABLE THERE IS A COLUMN_A,COLUMN_B
DECLARE @TBL TABLE (ID INT,
COLUMNNAME VARCHAR(50),
COLUMN_A VARCHAR(50),
COLUMN_B VARCHAR(50)
)

INSERT INTO @MAIN_TABLE VALUES
(1, 'VAL1 FOR NAME A',20),
(2, 'VAL2 FOR NAME A',30),
(3, 'VAL3 FOR NAME A',40)

INSERT INTO @TBL VALUES
(-1,'NAME', 'default VAL FOR NAME',25), --DEFAULT VALUE FOR NAME COLUMN IF ID DOES NOT EXIST IN @TBL TABLE
(-1,'AGE' ,'default VAL FOR AGE',100), --DEFAULT VALUE FOR AGE COLUMN IF ID DOES NOT EXIST IN @TBL TABLE
(1,'NAME','VAL FOR NAME A',200),
(1,'AGE','VAL FOR NAME A',300),
(2,'NAME','VAL FOR NAME A',400)

;WITH CTE
AS
(
SELECT
ID,
MAX(CASE WHEN COLUMNNAME = 'NAME' THEN COLUMN_A END) AS NAME_A,
MAX(CASE WHEN COLUMNNAME = 'NAME' THEN COLUMN_B END) AS NAME_B,
MAX(CASE WHEN COLUMNNAME = 'AGE' THEN COLUMN_A END) AS AGE_A,
MAX(CASE WHEN COLUMNNAME = 'NAME' THEN COLUMN_B END) AS AGE_B
FROM @TBL
GROUP BY ID
)

--SELECT * FROM CTE
SELECT --M.ID,
M.*,
ISNULL(T1.NAME_A, T2.NAME_A) NAME_A,
ISNULL(T1.NAME_B, T2.NAME_B) NAME_B,
ISNULL(T1.AGE_A, T2.AGE_A) AGE_A,
ISNULL(T1.AGE_B, T2.AGE_B) AGE_B
FROM @MAIN_TABLE M
LEFT JOIN CTE T1 ON T1.ID=M.ID
LEFT JOIN CTE T2 ON T2.ID = -1

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 14:22:10

see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-05-28 : 15:11:42

Thanks Visakh, I will check if converting to a dynamic pivot would be of any help.

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 01:12:20

ok...let us know if you need any more help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-05-29 : 13:50:25

How can I copy a screenshot or upload an image in my reply? I have made a graphical presentation of the input and a description of the requirement.

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:21:41

You can upload screenshot in some shared servers and then post the link here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-05-30 : 09:12:18

Ok thanks. I found this free online storage site. Here is the link to my image:

http://wikisend.com/download/450662/Untitled.gif

The 'Final Table' on the right is what the output needs to be. Please let me know if you have any questions or if you are not able to check the image.

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 02:49:59

I think your posted output has couple of typos. Anyways i think this should be approach


SELECT m.ID,m.NAME,m.AGE,
COALESCE(MAX(CASE WHEN mp.ID <> -1 THEN NAME_A END),MAX(CASE WHEN mp.ID = -1 THEN NAME_A END)) AS NAME_A,
COALESCE(MAX(CASE WHEN mp.ID <> -1 THEN NAME_B END),MAX(CASE WHEN mp.ID = -1 THEN NAME_B END)) AS NAME_B,
COALESCE(MAX(CASE WHEN mp.ID <> -1 THEN AGE_A END),MAX(CASE WHEN mp.ID = -1 THEN AGE_A END)) AS AGE_A,
COALESCE(MAX(CASE WHEN mp.ID <> -1 THEN AGE_B END),MAX(CASE WHEN mp.ID = -1 THEN AGE_B END)) AS AGE_B
FROM MainTable m
INNER JOIN (SELECT ID,
            MAX(CASE WHEN COLUMNNAME = 'NAME' THEN COLUMN_A END) AS NAME_A
            MAX(CASE WHEN COLUMNNAME = 'NAME' THEN COLUMN_B END) AS NAME_B,
            MAX(CASE WHEN COLUMNNAME = 'AGE' THEN COLUMN_A END) AS AGE_A
            MAX(CASE WHEN COLUMNNAME = 'AGE' THEN COLUMN_B END) AS AGE_B
            FROM MappingTable
            GROUP BY ID
           )mp
ON mp.ID = m.ID             
OR mp.ID = -1
GROUP BY m.ID,m.NAME,m.AGE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-05-31 : 09:24:57

Sorry about the typos. Ok I looked at your query and it applies the same logic as my initial posting but your's is written in a compact manner.
Thanks for your help.

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-05-31 : 09:52:31

Taking it a step further. The query you have written, what if I specify the number of columns, say N and your Select is built only using the top N columns based on their ordinal position and the rest of the columns fall in the next Select picking the next top N columns and so on.

For example: MainTable has 10 columns and N=3, we should output/print 4 Selects as each of the first three Selects will each have 3 columns and the last Select will be of 1 column.

Reason why I asking this is because a view will be created out of this final select query and since view has a limitation of 1024 columns, we are limiting the columns in the view and thus creating views based on the above logic. So for the above example 4 views will be created.
Please let me know if you need more clarification on this.

Any way to achieve this without looping, using only set based approach. I was trying to work out a logic and for a start the following is what I could come up with, but still miles to go:

--can execute this script in Sql2008 in ReportServer DB
use ReportServer;
DECLARE @TBL2 TABLE (COL VARCHAR(100),ROWNUM INT, [FIRSTROW] INT, [LASTROW] INT)

--No. of Columns required in the query
DECLARE @NUM INT = 7

;WITH CTE
AS
(
SELECT 1 AS [FirstRow], @NUM AS A
UNION ALL
SELECT A + 1, (@NUM + 1) + A - 1 FROM CTE WHERE [FirstRow] < 20 --total no. of columns in the table
)

INSERT INTO @TBL2
SELECT *
FROM
(
SELECT COLUMN_NAME, ROW_NUMBER()OVER(ORDER BY ORDINAL_POSITION) AS ROWNUM
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Catalog'
)TAB
CROSS APPLY (SELECT * FROM CTE)T
WHERE ROWNUM BETWEEN t.FirstRow AND t.A

SELECT [FIRSTROW] , [LASTROW] , LIST = STUFF((SELECT ', ' + COL
FROM @TBL2 b
WHERE b.[FIRSTROW] = a.[FIRSTROW]
AND b.[LASTROW] = a.[LASTROW]
FOR XML PATH('')), 1, 2, '')
FROM @TBL2 A
GROUP BY [FIRSTROW] , [LASTROW]

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 05:40:28

Sorry can you illustrate with data sample. I couldnt understand much from your explanation on N

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-02 : 08:38:15

Sorry for not being very clear on this, N=number of columns required in the Select query.In the sample example the variable @Num=N=no. of columns that need to be in the Select query, the order should be picked by Ordinal_Position column from information_Schema.columns.

For example: if N=@Num=3, Select query would be something like this-> Select Column1, Column2, Column3 From...thus in your query instead of 2 columns NAME, AGE we would have the first 3 columns of the table; the rest of the columns will follow the same logic, next Select would have next 3 columns and so on.

PSEDUO-CODE:
IF N=NUMBER OF COLUMNS TO BE INCLUDED IN THE SELECT QUERY=3 AND TOTAL NO. OF COLUMNS IN TABLE=10

NUMBER OF SELECT QUERIES = 10/3=3.3, SINCE IT HAS A REMAINDER ADD 1 TO INT(3.3)=3+1=4

FIRST SELECT - YOUR QUERY IS BUILT UPON ONLY THE FIRST 3 COLUMNS NUMBERED 1-3 BY ORDINAL_POSITION IN INFORMATION_SCHEMA.COLUMNS

SECOND SELECT - YOUR QUERY IS BUILT UPON THE NEXT 3 COLUMNS NUMBERED 4-6 BY ORDINAL_POSITION IN INFORMATION_SCHEMA.COLUMNS

THIRD SELECT - YOUR QUERY IS BUILT UPON THE NEXT 3 COLUMNS NUMBERED 7-9 BY ORDINAL_POSITION IN INFORMATION_SCHEMA.COLUMNS

FOURTH AND FINAL SELECT - YOUR QUERY PICKS UP THE NEXT 3 OR ANY REMAINING COLUMN(S) FROM INFORMATION_SCHEMA.COLUMNS

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 02:18:55

i think what you need is this??

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-03 : 11:52:35

Hi, you suggested this earlier and have tried the same. It does not work in my scenario but I guess we are past that now as the query is in place. The query you wrote is fine just that the number of columns need to be based on the logic I mentioned in the pseudo-code. I guess am not able to explain very clearly, what I will do now is make a pictorial presentation of the output. I am hoping that should clear things out.

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 00:03:18

Ok..that should make it much clearer for all of us

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-04 : 10:19:42

Hello, here is the link. I have taken your solution which you posted on 5/31:

http://wikisend.com/download/779280/sql_example.gif

Though I have been able to resolve this but not without a loop and I think I was on the right track with the example I posted on 5/31. But I would want your expertise to know if there is an efficient approach without a loop.

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 13:22:07

I think still the method to follow is as in link. Only difference is you should be having a logic based on information_schema.columns to get correct columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-06-04 : 13:29:45

Ok, thank you for your help and for bearing with me.

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources