September 6, 2013 at 12:01 pm
It doesn't look like Microsoft made it easy to pivot (like Matrix in SSRS) when the number of columns you end up with depends on the data.
Using the below data.. if I run by Destination.. when I choose Russell I would need to place 281, 282, and 374 in the pivot statement, but if I am running for Raceland I only need 490, and LEX.
The data I am working with will have between 10 and 75 columns.. and will depend on the data. I have not been able to find any solution that I can follow for how to pivot this data.
Note that I am using a CTE to pull the data together.. so it looks like the below.. and would need to pivot this on Code.
CREATE TABLE [dbo].[a_dcp](
[Destination_TX] [varchar](100) NULL,
[Code] [varchar](128) NULL,
[Arrived] [int] NULL,
[In_Trans] [int] NULL
) ON [PRIMARY]
insert into a_dcp
values ('Russell','281','0','1')
insert into a_dcp
values ('Russell','282','5','3')
insert into a_dcp
values ('Russell','281','9','1')
insert into a_dcp
values ('Ashland','281','11','9')
insert into a_dcp
values ('Ashland','374','1','15')
insert into a_dcp
values ('Ashland','490','10','1')
insert into a_dcp
values ('Ashland','Casco','1','1')
insert into a_dcp
values ('Raceland','490','13','12')
insert into a_dcp
values ('Raceland','Lex','30','22')
September 6, 2013 at 12:09 pm
Check out the articles in my signature about cross tabs. In particular you will want to focus on the dynamic cross tab article. It should help get you pointed right where you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 4:22 pm
This is as close and seems to work, now how would I show the Arrived .. In Transport. The columns are there.. Arrived first, and then In Transport, thanks to the Join.
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @HeadCol NVARCHAR(MAX), @HeadCol2 NVARCHAR(MAX);
SET @columns = N'';
set @HeadCol = N'';
set @HeadCol2 = N'';
select @HeadCol += N', aa.' + QUOTENAME(Code)
FROM (SELECT p.code FROM a_dcp AS p
GROUP BY p.code) AS x;
select @HeadCol2 += N', aa.' + QUOTENAME(Code)
FROM (SELECT p.code FROM a_dcp AS p
GROUP BY p.code) AS x;
SELECT @columns += N', p.' + QUOTENAME(Code)
FROM (SELECT p.code FROM a_dcp AS p
GROUP BY p.code) AS x;
SET @sql = N'
Select aa.Destination_TX, ' + STUFF(@Headcol, 1, 2, '') + ',' + STUFF(@Headcol2, 1, 2, '') + ' FROM (
SELECT Destination_TX, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT Destination_TX, code, arrived
FROM a_dcp
) p
PIVOT
(
SUM(Arrived) FOR Code IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p
) as aa
INNER JOIN (
SELECT Destination_TX, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT Destination_TX, code, In_Trans
FROM a_dcp
) p
PIVOT
(
SUM(In_Trans) FOR Code IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p
) as bb
on aa.Destination_TX = bb.Destination_TX';
PRINT @sql;
EXEC sp_executesql @sql;
September 6, 2013 at 4:23 pm
Man this is messy. Has MS fixed this in 2012? We are using 2005 and 2008.
September 6, 2013 at 5:26 pm
I modified the query so the columns will show if it is Arrived or In Transit, but the code works on my test... but not on my real data.
Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'N'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'as'.
Is there some cha, that I need to search my data for? That would crash the build of the SQL.
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @HeadCol NVARCHAR(MAX), @HeadCol2 NVARCHAR(MAX);
SET @columns = N'';
set @HeadCol = N'';
set @HeadCol2 = N'';
select @HeadCol += N', aa.' + QUOTENAME(Code) + ' as Arrived_' + Code
FROM (SELECT p.code FROM a_DCP AS p
GROUP BY p.code) AS x;
select @HeadCol2 += N', aa.' + QUOTENAME(Code) + ' as In_Trans_' + Code
FROM (SELECT p.code FROM a_DCP AS p
GROUP BY p.code) AS x;
SELECT @columns += N', p.' + QUOTENAME(Code)
FROM (SELECT p.code FROM a_DCP AS p
GROUP BY p.code) AS x;
print @HeadCol
SET @sql = N'
Select aa.Destination_TX, ' + STUFF(@Headcol, 1, 2, '') + ',' + STUFF(@Headcol2, 1, 2, '') + ' FROM (
SELECT Destination_TX, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT Destination_TX, code, arrived
FROM a_DCP
) p
PIVOT
(
SUM(Arrived) FOR Code IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p
) as aa
INNER JOIN (
SELECT Destination_TX, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT Destination_TX, code, In_Trans
FROM a_DCP
) p
PIVOT
(
SUM(In_Trans) FOR Code IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p
) as bb
on aa.Destination_TX = bb.Destination_TX';
PRINT @sql;
EXEC sp_executesql @sql;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply