Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Pivot without an aggregate


How to Pivot without an aggregate

Author
Message
dndaughtery
dndaughtery
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 1071
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.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7007 Visits: 13559
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
dndaughtery
dndaughtery
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 1071
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.
Florian Reischl
Florian Reischl
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1943 Visits: 3934
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7007 Visits: 13559
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
dndaughtery
dndaughtery
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 1071
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7007 Visits: 13559
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
dndaughtery
dndaughtery
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 1071
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.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7007 Visits: 13559
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
Brian Dooley
Brian Dooley
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search