Solved

Need help with TSQL - Pivoting columns using PIVOT or something else

Posted on 2013-06-28
14
330 Views
Last Modified: 2013-06-28
i have data coming from a large query that needs to be transposed. Piviot might not work in this instance, and either way i havent been able to get it to work. This cannot be done in SSRS, it has to be straight from the DB, output as a table for another application to use.

this result is currently generated by dynamic sql, as the USERCURR columns is dynamic based on the calling app. lets say there are columns USERCURR1 through USERCURR10. Not all columns will have data.

The Original table will have some static columns that need to remain, and then instead of the USERCURR columns, they are replaced with two columns, BUCKETNAME and BUCKETAMT. For each row, it get SPLIT into multiple rows based on the number of USERCURR columns. I have attached a spreadsheet which clearly explains the need.

example-for-ee.xlsxexample-for-ee.xlsx

Ideally, if the USERCURR column is null or 0 we would not include it, but thats ok as it could be cleaned up later
0
Comment
Question by:mikegrad7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39285137
It sounds like you need the UNPIVOT operator and not the PIVOT one.

Essentially:
UNPIVOT (
    BUCKETAMT
    FOR BUCKETNAME
    IN (USERCURR1, USERCURR2, USERCURR3, USERCURR4, USERCURR5, 
        USERCURR6, USERCURR7, USERCURR8, USERCURR9, USERCURR10)
) upvt

Open in new window


In your dynamic SQL, you could do something like this:
@sql = '...
UNPIVOT (
    BUCKETAMT
    FOR BUCKETNAME
    IN (' + @columns + ')
) upvt
...'

I hope that helps!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39285207
:) this is an UNpivot, however the code I'm proposing does not use the 'unpivot' feature, instead it uses a different technique which is very efficient to achieve this type of result. It uses a combination of cross apply and 'values'.

The result of this code applied to your sample is:
FUND	INVESTOR OPERATIONNUMBER BUCKETNAME	BUCKETAMT
Fund 1		Inv1	1	USERCURR1	1234
Fund 1		Inv1	1	USERCURR2	237
Fund 1		Inv2	1	USERCURR15	2789
Fund 1		Inv2	1	USERCURR16	1298
Fund 1		Inv3	1	USERCURR2	986
Fund 1		Inv3	1	USERCURR16	432
Fund 1		Inv1	2	USERCURR1	3266
Fund 1		Inv1	2	USERCURR2	674
Fund 1		Inv1	2	USERCURR15	3456
Fund 1		Inv2	2	USERCURR1	6789
Fund 1		Inv2	2	USERCURR15	12567
Fund 1		Inv2	2	USERCURR16	7841

Open in new window

The SQL for this is:
SELECT
      Fund
    , Investor
    , OperationNumber
    , BUCKETNAME
    , BUCKETAMT
FROM original
CROSS APPLY (
	          VALUES
                ('USERCURR1', USERCURR1)
              , ('USERCURR2', USERCURR2)
              , ('USERCURR15', USERCURR15)
              , ('USERCURR16', USERCURR16)
            ) AS CrossApplied(BUCKETNAME , BUCKETAMT)
WHERE bucketamt IS NOT NULL

Open in new window

this may be seen/used at http://sqlfiddle.com/#!3/b9a0f/1

there are further references for this technique here. Note this technique is marginally faster than the 'unpivot' (elapsed) and consumes less cpu (results An Alternative (Better?) Method to UNPIVOT (SQL Spackle) By Dwain Camps, 2012/08/02)

As you can see this isn't dynamic code, but it can be made into dynamic.
{+edit copy/paste problem - fixed}
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39285220
oh, I see I have missed a bit, the 'line identifier' - sorry - back soon I hope
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39285257
This implememts the 'line number'. Note this is actually based on dense_rank() as you require a repeat of a line number - which this conveniently provides.
FUND	INVESTOR	OPERATIONNUMBER	BUCKETNAME	BUCKETAMT	LINE_NUMBER
Fund 1	Inv1	1	USERCURR1			1234		1
Fund 1	Inv1	1	USERCURR2			237		1
Fund 1	Inv1	2	USERCURR1			3266		2
Fund 1	Inv1	2	USERCURR2			674		2
Fund 1	Inv1	2	USERCURR15			3456		2
Fund 1	Inv2	1	USERCURR15			2789		3
Fund 1	Inv2	1	USERCURR16			1298		3
Fund 1	Inv2	2	USERCURR1			6789		4
Fund 1	Inv2	2	USERCURR15			12567		4
Fund 1	Inv2	2	USERCURR16			7841		4
Fund 1	Inv3	1	USERCURR2			986		5
Fund 1	Inv3	1	USERCURR16			432		5

-- code for the above result:
SELECT
      Fund
    , Investor
    , OperationNumber
    , BUCKETNAME
    , BUCKETAMT
    , dense_rank() over (ORDER BY Fund, Investor, OperationNumber) AS line_number
FROM original
CROSS APPLY (
	          VALUES
                ('USERCURR1',  USERCURR1)
              , ('USERCURR2',  USERCURR2)
              , ('USERCURR15', USERCURR15)
              , ('USERCURR16', USERCURR16)
            ) AS CrossApplied(BUCKETNAME , BUCKETAMT)
WHERE bucketamt IS NOT NULL

Open in new window

see http://sqlfiddle.com/#!3/a6d98/1
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39285262
PortletPaul's example reminded me of some key points:
- Ensure the columns have the same data types and length. If not, you may want to have a derived table or common table expression handle CONVERT before you denormalize the data.
- Confirm the value in the empty columns is really NULL and not zero or empty string. If either of the latter two, then you can again use an intermediary query to massage the data before you UNPIVOT.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39285320
Excellent points, yes there is an inherent assumption your can "stack" these values into a column hence they need to share data type. Good catch mwvisa1.
(do I detect some experience there?)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39285328
actually, you could also do some tidy-up in the cross apply if needed, e.g.
CROSS APPLY (
                VALUES
                ('USERCURR1',  convert(decimal (18,2),USERCURR1)) -- e.g.
              , ('USERCURR2',  USERCURR2)
              , ('USERCURR15', USERCURR15)
              , ('USERCURR16', USERCURR16)
            ) AS CrossApplied(BUCKETNAME , BUCKETAMT)
0
 
LVL 1

Author Comment

by:mikegrad7
ID: 39285416
wow, you guys are fast, i need to dig thru these and see what works.

FYI, the line number in the spreadsheet is just for clarity, its not part of the data set.

I am still a bit confused on how i keep the initial columns the same, but ill run some tests.

Thanks
Mike
0
 
LVL 1

Author Comment

by:mikegrad7
ID: 39285422
one other comment, my original table is actualyl the result of some dynamic SQL. Im having trouble getting that into a temp table due to the dynamic SQL issues, and then accessing that table later. I know it adds a bit of a wrinkle. Right now i am going to try and as my dynamic sql as the original table part
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39285433
Ah, do you not have access to edit the original dynamic SQL? The reasoning behind my showing you the UNPIVOT syntax is that you probably already have a variable in the dynamic SQL code with the column names, which you said is variable. Leveraging this columns variable, you can have the dynamic SQL pass you the table already denormalized.

If no, how are you getting the dynamic SQL results? Is it from a stored procedure?
0
 
LVL 1

Author Comment

by:mikegrad7
ID: 39285464
no, i have the dynamic SQL string, i was trying to insert the results to a temp table, inside a stored proc, but a few tests have shown that i can do this whole thing in one step, with the sqyamic sql select statement after the from  (  sql) as original
0
 
LVL 1

Author Closing Comment

by:mikegrad7
ID: 39285760
this help was fantastic. I have things working as expected.
0
 
LVL 1

Author Comment

by:mikegrad7
ID: 39285772
Thanks to everyone and PortletPaul with the best answer! I have this working now.

The dynamic SQL ended up looking something like this
      
	select @sql1 = '
	SELECT
	OPERATION_ID  ,	EFFECTIVE_DATE ,
	APPROVED
FROM 
(	SELECT A.IQID as OPERATION_ID,A.CLOSEDATE as EFFECTIVE_DATE,A.USERBOOL1 as APPROVED,A.DRAFT as DRAFT,' + @bucket_list_string + 'FROM (someplace) ) as original
CROSS APPLY ( VALUES ' + @split_bucket_string + ' ) AS CrossApplied(BUCKETNAME , BUCKETAMT)
WHERE bucketamt IS NOT NULL order by SOMEVALUE'
         

Open in new window


then i just execute(@sql1).
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39286038
thanks!, and good work making it dynamic. Cheers, Paul
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question