Version: Microsoft SQL Server 2014
I have successfully created a dynamic pivot table (with help), and now I have a question regarding the column names and their values.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ','
+ QUOTENAME('Component_' + cast(rn as varchar(10)))
FROM dbo.table
CROSS APPLY
(SELECT row_number() over(partition by UPC order by ComponentNum) rn
FROM dbo.table) x
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = 'SELECT UPC, ' + @cols + ' FROM
(
SELECT UPC, ComponentNum,
''Component_''
+ cast(row_number() over(partition by UPC order by ComponentNum) as varchar(10)) val
FROM dbo.table) x
PIVOT
(
MAX(ComponentNum)
FOR val IN (' + @cols + ')
) p '
execute(@query)
After the table was pivoted, it created 27 columns of Component_X.
The 27 columns is supposed to represent different kinds of parts that is associated to a unique UPC number. Not all UPCs have 27 kinds of parts.
It seems that depending on what row number the part was listed in the partitioned group of UPC numbers is where it was assigned in the pivot column.
That indicates to me that I need to sort the original data before running the pivot, no? Looking at the result table below, you can see that part number 543 is shown under Component_13, Component_1, and Component_10 for different UPC numbers.
ID UPC Component_13 Component_1 Component_10
------------------------------------------------------------------------
1 123 543 NULL 345
2 321 345 543 765
3 213 654 345 NULL
4 312 765 NULL 543
My problem is that I cannot allow the part number to float between different columns. If the part number is a "Component_13" type then it needs to stay in that column.
My goal is to have each iteration of Component_X represent a specific kind of part.
Component_1 = Bolts
Component_2 = Nuts
Component_3 = Washers
The other problem is that I also need to enumerate multiple "Bolt" part numbers into their own column for retrieval by another piece of software. Any UPC number could have any number of "Bolts" of different sizes with different part numbers.
End result table could look like:
ID UPC Bolt1 Bolt2 Bolt3 Nut1 Nut2
------------------------------------------------------------------------
1 123 1.5 1 NULL 0.5 .375
2 321 2.0 NULL NULL .625 NULL
3 213 0.25 .875 .375 NULL NULL
4 312 NULL NULL NULL 1.25 .625
This is probably not possible, but I had to ask if anyone could help with a solution.
You could use REPLICATE to generate a two or more digits number that can be easily ordered.
create table test(rn varchar(10));
insert into test
values ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10'), ('11'), ('12');
select num
from
(select 'Component_' + replicate('0', 2 - len(rn)) + rn as num
from test) x
order by num;
Result:
| num |
| :----------- |
| Component_01 |
| Component_02 |
| Component_03 |
| Component_04 |
| Component_05 |
| Component_06 |
| Component_07 |
| Component_08 |
| Component_09 |
| Component_10 |
| Component_11 |
| Component_12 |
dbfiddle here