Reputation: 143
I have a simple table of non-unique account numbers, product IDs, and quantities:
For example:
account|productid|qty
1 100 1
1 100 1.5
1 102 6
2 100 1
I'm trying to get this to be pivoted dynamically into this kind of structure:
account|product1|qty1|product2|qty2|etc..|etc..
1 100 2.5 102 6 NULL NULL
2 100 1 NULL NULL NULL NULL
Some of these customers can have ordered hundreds of different products, so trying to hard-code things ended up being out of the question.
I've managed to pivot this into a table like
account|100|102
1 2.5 6
2 1 NULL
with product IDs as column headers,
with:
DECLARE @sql AS NVARCHAR(4000)
, @col AS NVARCHAR(2000);
SELECT @col = ISNULL(@col + ', ', '') + QUOTENAME(x.productid)
FROM
(
SELECT DISTINCT
tp.productid
FROM purchases AS tp
) x
;
SET @sql
= N'SELECT * FROM purchases as p PIVOT ( SUM(qty) FOR [productid] IN (' + @col
+ ')) piv';
SELECT @sql;
EXEC sys.sp_executesql @sql;
I attempted to select the same columns twice to get a qty and product ID for each, by changing my select statement to:
@coltest = ISNULL(@col + ', ', '') + (QUOTENAME(x.productid) + ', ' + QUOTENAME(x.productid))
however was thrown an error that 'productid was specified multiple times for 'piv'.'
What's the best way to approach pivoting into two seperate custom-named incrementing columns?
Upvotes: 0
Views: 75
Reputation: 81930
No need to Pivot twice.
Example
Declare @SQL varchar(max) = '
Select *
From (
Select A.Account
,B.*
From (Select Account
,ProductID
,Qty = sum(Qty)
,RN=Row_Number() over (Partition By Account Order by ProductID)
From YourTable
Group By Account,Productid
) A
Cross Apply (values (''qty''+cast(RN as varchar(25)),cast(Qty as varchar(100)))
,(''product''+cast(RN as varchar(25)),cast(productid as varchar(100)))
) B (Item,Value)
) A
Pivot (max([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName('product'+ColNr)
+','+QuoteName('qty'+ColNr)
From (Select Distinct ColNr=cast(Row_Number() over (Partition By Account,ProductID Order by (Select NULL)) as varchar(25)) From YourTable ) A
Order By 1
For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
Print @SQL
Returns
If it helps with the Visusalization - The subquery produces
Upvotes: 2