Please help me create a select SQL statement with the results column name get from the column values in origin table (tablename is Device_Part):
User can input many DeviceCode which have many dynamic PartTypeName, the PartTypeName value is the PartInfo.
This may help:
CREATE Table Device (
DeviceCode NVARCHAR(100) NOT NULL,
PartTypeName NVARCHAR(100) NOT NULL,
PartInfo NVARCHAR(100) NOT NULL
)
Insert Into Device
Values('VT.SX-01','CPU','Pentium G6650'),
('VT.SX-01','Motherboard','H81M - S2PV'),
('VT.SX-01','RAM','DDR# 4GB - bus 1866 - Nano'),
('VT.SX-01','PartType Name 01','PartInfo 01')
--QUERY
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnNamesInPivot AS NVARCHAR(MAX);
--Get distinct values of PIVOT Column
SELECT @ColumnNamesInPivot = ISNULL(@ColumnNamesInPivot + ',', '')
+ QUOTENAME([PartTypeName])
FROM ( SELECT DISTINCT
[PartTypeName]
FROM Device
) AS P
SELECT @DynamicPivotQuery = N'Select DeviceCode,'
+ @ColumnNamesInPivot + '
FROM ( SELECT *
FROM Device
) AS SourceTable PIVOT( MAX(PartInfo) FOR [PartTypeName] IN ('
+ @ColumnNamesInPivot + ') ) AS PVTTable'
EXEC sp_executesql @DynamicPivotQuery;
And the result will be:
Try this
;WITH _CTE(_DeviceCode,_PartTypeName,_PartInfo,_Id)
AS
(
SELECT DeviceCode,PartTypeName,PartInfo ,ROW_NUMBER() OVER (PARTITION BY PartTypeName ORDER BY Id) FROM Your_tablename
)
SELECT *
FROM
(
SELECT _DeviceCode,_PartTypeName,_PartInfo
FROM _CTE
)C
PIVOT
(
MAX(_PartInfo) FOR _PartTypeName IN ([CPU],[MotherBoard],[RAM],[PartTypeName])
) AS PivotTable;
