May 26, 2009 at 10:56 am
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.
May 26, 2009 at 11:15 am
Hi,
maybe you could start with the code snippets from this post http://www.sqlservercentral.com/Forums/FindPost714447.aspx
The problem looks similar.
May 26, 2009 at 11:23 am
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.
May 26, 2009 at 12:11 pm
Use MAX or MIN. This also works for VARCHAR.
May 26, 2009 at 12:42 pm
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
*/
May 26, 2009 at 12:54 pm
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
May 26, 2009 at 1:07 pm
Just change the col type of caseid in #test to uniqueidentifier.
May 26, 2009 at 3:48 pm
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.
May 27, 2009 at 3:56 am
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."
November 5, 2009 at 1:12 pm
SSCommitted,
Just wanted to thank you for your code. It was perfect for what I was trying to do!
Thanks,
BDooley
April 26, 2011 at 11:03 am
Thanks you for this example! You saved me some time since I was able to modify for a similar Pivot without aggregation.
December 26, 2013 at 11:46 am
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.
December 26, 2013 at 1:23 pm
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
Change is inevitable... Change for the better is not.
December 27, 2013 at 3:44 am
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
December 27, 2013 at 7:27 am
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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply