Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to Pivot without an aggregate Expand / Collapse
Author
Message
Posted Tuesday, May 26, 2009 10:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
I have data like the following format:


caseID Code
1 AAA
1 BBB
1 CCC
1 DDD
2 CCC
3 AAA
3 BBB
3 CCC
3 DDD
3 EEE

I want to pivot it so that it displays as such:


CaseID Code1 Code2 Code3 Code4 Code5
1 AAA BBB CCC DDD
2 CCC
3 AAA BBB CCC DDD EEE

How can I do this? I was trying to use the Pivot Transform in ssis but it seems to need an aggregate.
Post #723433
Posted Tuesday, May 26, 2009 11:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:33 AM
Points: 6,932, Visits: 12,656
Hi,

maybe you could start with the code snippets from this post http://www.sqlservercentral.com/Forums/FindPost714447.aspx
The problem looks similar.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #723446
Posted Tuesday, May 26, 2009 11:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
It doesn't help. In that example they know ahead of time how many values there will be and it uses the sum aggregate. I can't use any aggregates. I'm basically wanting to place the varchars into the row for each caseID.
Post #723451
Posted Tuesday, May 26, 2009 12:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Use MAX or MIN. This also works for VARCHAR.


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #723487
Posted Tuesday, May 26, 2009 12:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:33 AM
Points: 6,932, Visits: 12,656
Hi,

I didn't notice that the resulting columns need to be dynamic. Sorry.

As far as I know, dynamic pivots only can be done using dynamic SQL.

In order to sort the result values and to built and reference the column names I used a CTE to add a row number per caseid and code.

-- step 1: create separate table that will hold the row_number per code
CREATE TABLE #test (caseid INT, row INT, code CHAR(3))

-- step 2: fill the temp table with test data and row_number
;WITH CTE_test(caseid, row, code)
AS
(
SELECT caseid,
row_number() OVER(PARTITION BY caseid ORDER BY caseid, code),
code
FROM test
)
INSERT INTO #test
SELECT caseid, row, code
FROM CTE_test

-- step 3: build dynamic SQL
DECLARE @sql nvarchar(max)

SET @sql = N'SELECT caseid'

SELECT @sql = @sql + ',MAX(CASE WHEN row='+CAST(a.row AS CHAR(5))+' THEN code ELSE '''' END) AS [Col'+CAST(a.row AS CHAR(5))+']'
FROM #Test a GROUP BY a.row
ORDER BY a.row

SET @sql = @sql + N'
FROM #Test
GROUP BY caseid
ORDER BY caseid'
PRINT @sql -- For Debugging
--EXEC sp_executesql @sql

/* results
caseid Col1 Col2 Col3 Col4 Col5
1 AAA BBB CCC DDD
2 CCC
3 AAA BBB CCC DDD EEE
*/





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #723514
Posted Tuesday, May 26, 2009 12:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
My CaseID is actually a uniqueidentifier type. How can I update it to work with that? I tried to change the declaration to uniqueidentifier but it gives me the following error


Operand type clash: uniqueidentifier is incompatible with int
Post #723523
Posted Tuesday, May 26, 2009 1:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:33 AM
Points: 6,932, Visits: 12,656
Just change the col type of caseid in #test to uniqueidentifier.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #723537
Posted Tuesday, May 26, 2009 3:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
That worked until my dynamic string became larger than 8000 characters. IS there any way to do it without the dynamic statement being created on the fly? I'm thinking the best bet I have is to create anothe temp table and loop thourgh it.
Post #723657
Posted Wednesday, May 27, 2009 3:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:33 AM
Points: 6,932, Visits: 12,656
That's strange...

The @sql variable is declared as nvarchar(max). So it should allow to store more than 8000 character.
Could you provide sample data (as attached file) that would (slightly) exceed the 8K limit of the resulting SQL string?

As per BOL:
"varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.
...
Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes."




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #723919
Posted Thursday, November 05, 2009 1:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 26, 2011 10:46 AM
Points: 6, Visits: 160
SSCommitted,

Just wanted to thank you for your code. It was perfect for what I was trying to do!

Thanks,

BDooley
Post #814521
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse