Konvertieren von Zeilen in konstante Spalten in SQL server?

Ich habe die folgende Abfrage mit dem Ergebnis:

SELECT * FROM dbo.DeviceView AS dv WHERE DeviceId = 5 

Ergebnis:

 Id Name AttachId ColorId Date --- ------- ---------- ------- ------- 5 Apple iPhone 5s A1533 NULL 1 2013-09-10 00:00:00.000 5 Apple iPhone 5s A1533 NULL 8 2013-09-10 00:00:00.000 5 Apple iPhone 5s A1533 NULL 19 2013-09-10 00:00:00.000 

ColorId ist in verschiedenen Werten und es kann mehr oder weniger als 3 Werte sein
Ich möchte ColorId in 3 Spalten umwandeln, wie ColorId1 ersten Wert in ColorId1 und den zweiten Wert in ColorId2 und den dritten Wert in ColorId3 .
z.B:

 Id Name AttachId ColorId1 ColorId2 ColorId3 Date --- ------- ---------- ---------- ---------- ---------- ------- 5 Apple iPhone 5s A1533 NULL 1 8 19 2013-09-10 00:00:00.000 

Wie kann ich es in folgendes umwandeln?

Bearbeiten :
Alle anderen Felder außer ColorId sind gleich.

Sie können diese generische Abfrage auf der Basis von PIVOT und CTE verwenden, die sich problemlos um beliebig viele colors erweitern können und das sehr gut funktioniert:

 -- First, we assign unique numbers to each of the ColorId's. These will become column names ;WITH NumberedColors (ColorId,ColorNumber) AS ( SELECT ColorId,'Color'+CAST((ROW_NUMBER() OVER (ORDER BY ColorId)) AS VARCHAR) AS ColorNumber FROM dbo.DeviceView GROUP BY ColorId ), -- Here we return the dbo.DeviceView extended with ColorNumber column name DeviceViewWithNumberedColors (Id,Name,AttachId,[Date],ColorNumber,ColorId) AS ( SELECT Id,Name,AttachId,Date,NC.ColorNumber,NC.ColorId FROM dbo.DeviceView DV INNER JOIN NumberedColors NC ON DV.ColorId=NC.ColorId ) -- Finally, we use the PIVOT to assign color's to the appropriate columns SELECT * FROM ( SELECT Id,Name,AttachId,[Date],ColorId,ColorNumber FROM DeviceViewWithNumberedColors D ) AS Source PIVOT ( SUM(ColorId) FOR ColorNumber IN ([Color1],[Color2],[Color3],[Color4],[Color5],[Color6],[Color7],[Color8],[Color9],[Color10]) ) Piv 

In der PIVOT-Klausel ist darauf zu achten, dass Sie genug Farbspalten haben. Wenn dies nicht hartcodiert werden kann, dh die Anzahl der colors könnte über eine feste Zahl hinaus wachsen, dann verwenden Sie dynamisches SQL, um diese Abfrage zu generieren.

Wenn Sie genau drei colors wollen, können Sie bedingte Aggregation oder Pivot verwenden:

 select id, name, attachid, max(case when seqnum = 1 then color end) as color1, max(case when seqnum = 2 then color end) as color2, max(case when seqnum = 3 then color end) as color3, date from (select t.*, row_number() over (partition by id order by (select null)) as seqnum from t ) t group by id, name, attachid, date; 

Wenn die Anzahl der ColorId-Werte keine feste Nummer ist, müssen Sie dynamische sql verwenden.

 DECLARE @sql nvarchar(MAX) = 'SELECT Id, Name, AttachId' SELECT @sql = @sql + ', MAX(IIF(ValueNum = ' + LTRIM(STR(ColumnNum)) + ', ColorId, NULL)) AS ColorId' + LTRIM(STR(ColumnNum)) FROM ( SELECT DISTINCT DENSE_RANK() OVER (ORDER BY ColorId) AS ColumnNum FROM dbo.DeviceView WHERE Id IN ( SELECT TOP 1 Id FROM dbo.DeviceView GROUP BY Id ORDER BY COUNT(DISTINCT ColorId) DESC) ) AS c EXEC (@sql + ', [Date] FROM (' + 'SELECT Id, Name, AttachId, ColorId, [Date], DENSE_RANK() OVER (PARTITION BY Id ORDER BY ColorId) AS ValueNum FROM dbo.DeviceView' + ') AS it ' + 'GROUP BY Id, Name, AttachId, [Date]');