How to Pivot without an aggregate

Question

Post reply

How to Pivot without an aggregate

dndaughtery

SSChampion

Points: 11290
More actions
May 26, 2009 at 10:56 am

#134587

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.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dndaughtery SSChampion Points: 11290 More actions · Answer 2

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 SSC-Dedicated Points: 37301 More actions · Answer 3

Florian Reischl

SSC-Dedicated

Points: 37301

May 26, 2009 at 12:11 pm

#1000088

Use MAX or MIN. This also works for VARCHAR.

LutzM SSC Guru Points: 107049 More actions · Answer 4

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

caseidCol1Col2Col3Col4Col5

1AAABBBCCCDDD

2CCC

3AAABBBCCCDDDEEE

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dndaughtery SSChampion Points: 11290 More actions · Answer 5

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 SSC Guru Points: 107049 More actions · Answer 6

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dndaughtery SSChampion Points: 11290 More actions · Answer 7

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 SSC Guru Points: 107049 More actions · Answer 8

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Brian Dooley SSC Rookie Points: 46 More actions · Answer 9

SSCommitted,

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

Thanks,

BDooley

JMagargal SSC Rookie Points: 49 More actions · Answer 10

Thanks you for this example! You saved me some time since I was able to modify for a similar Pivot without aggregation.

asheppardwork SSCommitted Points: 1667 More actions · Answer 11

Thank you so much for your code. I have been looking for two days for someone to list a simple example of how to "pivot" rows and columns WITHOUT aggregates. I know its not truly a pivot in the SQL/Excel point of view, but I still feel the terminology holds up as it is turning row data into columnar data. I have seen many bad examples; and almost all of them aggregate or have a fixed number of columns. Yours is the only one with the three elements I needed, dynamic columns, change of row to column, and NO- Aggregation. I know I can't be the only one who has to come up with lists like say customer/part no. where you want jus the list of customer once and the part no. in columns out to the right but the data is formatted to be 1 to 1 customer/part no. which repeats customers over and over.

Thank you again.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

asheppardwork (12/26/2013)
Thank you so much for your code. I have been looking for two days for someone to list a simple example of how to "pivot" rows and columns WITHOUT aggregates. I know its not truly a pivot in the SQL/Excel point of view, but I still feel the terminology holds up as it is turning row data into columnar data. I have seen many bad examples; and almost all of them aggregate or have a fixed number of columns. Yours is the only one with the three elements I needed, dynamic columns, change of row to column, and NO- Aggregation. I know I can't be the only one who has to come up with lists like say customer/part no. where you want jus the list of customer once and the part no. in columns out to the right but the data is formatted to be 1 to 1 customer/part no. which repeats customers over and over.
Thank you again.

Just so you know, MAX is considered to be a form of an "Aggregate". I wanted to clarify that because when people ask me how to pivot without an aggregate in T-SQL, I tell them it's not possible... you need an aggregate in on form or another.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

amrut.kumbar 7070 SSC Veteran Points: 249 More actions · Answer 13

create table CaseCode(

caseID int, Code varchar(10))

insert into CaseCode values

(1 ,'AAA')

,(1 ,'BBB')

,(1 ,'CCC')

,(1 ,'DDD')

,(2 ,'CCC')

,(3 ,'AAA')

,(3 ,'BBB')

,(3 ,'CCC')

,(3 ,'DDD')

,(3 ,'EEE')

select * from CaseCode

select caseID, AAA as code1,BBB as code2,CCC as code3,DDD as code4 from

(select caseid,code from CaseCode)

a pivot

(max(code) for code in (AAA,BBB,CCC,DDD))AS pivo

order by caseID

asheppardwork SSCommitted Points: 1667 More actions · Answer 14

Thank you for the clarification, I was aware that MAX is an aggregate; but you use it in such a way as to allow each row to be its own max and hence getting results for every row. I was excited yesterday because the longer I searched the farther away I got from the answer. It seems that it is hard for most SQL users to believe there are legitimate reasons for needing a long list of items with multiple instances to be made into a short list in SQL. Its almost as if no one else does ERP type reporting with quarterly figures that compare year over year figures for a variable and constantly changing group of people; which I know is not true.

One example, my own, is that I am producing a SSRS report in which I need to show a salespersons name and then the id next to it. However in Microsoft's Dynamics GP they allow sales people to have multiple ids; so "Jim Smith" can have ids "1234,1234z,1245x,1264" and if you need a list without the person being listed four times; you can have SSRS do the work or SQL. The rub comes in when you have 100+ sales people and each with a combination of between 1 and 9 ids and no control over whether or not to clean up the data or if the ids have a set pattern. Now your looking at just for two fields returning over 800+ rows and then having SSRS do the logic at runtime. The overall effect is a slow report that at best must be cached each night. Now say that management requires said report to be able to run ad-hoc during the production day with the latest information. Suddenly all the answers of SQL shouldn't be doing this or let SSRS handle it etc. sound academic and rather unhelpful. That is where your code comes in, now I can add it to my rather lengthy Stored Proc. and return just 100 records with name and id(s) at the beginning followed by other un-related data and the report runs quickly and quietly with no matrix pivot; sub-report; mish-mash for the reporting engine to translate before producing results.

In the future, I will be searching here first; for whatever reason sql server central does not get very good results in google or duckduckgo for specifics only on the generalities.