July 3, 2018 at 10:04 am
I have the following data:
itemID Description column Name column index value
00000230 "Pants Davi-s Professional Wear " "Men sizes " 0 46
00000230 "Pants Davi-s Professional Wear " "Summer colors" 1 BLUE
00000231 "Pants Davi-s Professional Wear " "Men sizes" 0 46
00000231 "Pants Davi-s Professional Wear " "Summer colors " 1 ORANGE
00000232 "Pants Davi-s Professional Wear " "Men sizes " 0 46
00000232 "Pants Davi-s Professional Wear" "Summer colors" 1 PINK
How can i arrange it as:
itemID Description Men sizes Summer colors
00000230 "Pants Davi-s Professional Wear" 46 "blue"
00000231 "Pants Davi-s Professional Wear" 46 "orange"
00000231 "Pants Davi-s Professional Wear " 46 "PINK"
There can be more columns depending on how many column index's there are.
I have been looking at pivot tables but cant seem to get them to work with this data.
Any help would be appreciated.
Thanks
July 3, 2018 at 12:17 pm
Try a dynamic pivot
Drop table if Exists #T
Create table #T(itemID varchar(20),
Description varchar(100),
Title varchar(20),
IndexID smallint,
ItemValue varchar(30))
insert into #T values
('00000230','Pants Davi-s Professional Wear','Men sizes',0,'46'),
('00000230','Pants Davi-s Professional Wear','Summer colors',1,'BLUE'),
('00000231','Pants Davi-s Professional Wear','Men sizes', 0,'46'),
('00000231','Pants Davi-s Professional Wear','Summer colors',1,'ORANGE'),
('00000232','Pants Davi-s Professional Wear','Men sizes',0,'46'),
('00000232','Pants Davi-s Professional Wear','Summer colors',1,'PINK')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Title)
FROM #T c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ItemID, Description, ' + @cols + ' from
(
select ItemID, Description, ItemValue, Title
from #T
) x
pivot
(
max(ItemValue)
for Title in (' + @cols + ')
) p '
execute(@query)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 4, 2018 at 4:44 am
Thank you. This helped me alot.
July 4, 2018 at 5:23 am
An alternative is to use the "dynamic crosstab" query. For example, using the temporary table provided above, something like:
SELECT @query = 'SELECT ItemID, Description ';
SELECT @query = @query + d.Caseline
FROM
(
SELECT DISTINCT
(', MAX(CASE WHEN IndexID = ' + CONVERT(CHAR(5), IndexID) + ' THEN ItemValue END) AS ' + QUOTENAME(Title)) AS Caseline
FROM #T
) d;
SELECT @query = @query + ' FROM #t GROUP BY ItemID, Description';
EXECUTE (@query);
The query above appears to be significantly less expensive than the XML-based version above.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply