SQL转换数据(SQL transform data)

SQL转换数据(SQL transform data)

我有以下格式的查询结果

ID CODE ---------- 1 abc 1 xyz 1 def 1 pqr 1 jkl 1 tuv

我希望结果采用以下格式

ID CODE1 CODE2 CODE3 CODE4 CODE5 CODE6 --------------------------------------- 1 abc xyz def pqr jkl tuv

我知道这可以使用静态PIVOT查询来实现，但是我面临的问题是CODE列可能有未知值，我希望我的列名是固定的，即CODE1 ， CODE2等等直到CODE6 。

有人能帮我解决这个问题吗？

欢迎任何建议。

谢谢。

I have results coming from a query in the below format

ID CODE ---------- 1 abc 1 xyz 1 def 1 pqr 1 jkl 1 tuv

I want the results to be in the following format

ID CODE1 CODE2 CODE3 CODE4 CODE5 CODE6 --------------------------------------- 1 abc xyz def pqr jkl tuv

I know this can be achieved using static PIVOT query, but the issue I am facing is that the CODE column can have unknown values and I want my column names to be fixed i.e. CODE1, CODE2 and so on 'til CODE6.

Can someone please help me out with this?

Any suggestions are welcome.

Thank you.

最满意答案

这可以是使用ROW_NUMBER()窗口函数的相当简单的PIVOT。 我为ID 1投入了第七个CODE ，以表明如果你只是旋转6列，它将被忽略，但是，因为你的OVER不是确定性的（它可以以任何方式进行排序，因为它只使用ID ），它可以选择一组不同的6个CODE 。

SQL小提琴

MS SQL Server 2017架构设置 ：

CREATE TABLE t1 ( ID int, _CODE varchar(20) ) ; INSERT INTO t1 (ID, _CODE) VALUES (1,'abc') , (1,'xyz') , (1,'def') , (1,'pqr') , (1,'jkl') , (1,'tuv') , (2,'lmn') , (2,'rgb') , (1,'ignoredOnly6') ;

查询1 ：

SELECT piv.ID , piv.[1] AS CODE1 , piv.[2] AS CODE2 , piv.[3] AS CODE3 , piv.[4] AS CODE4 , piv.[5] AS CODE5 , piv.[6] AS CODE6 FROM ( SELECT t1.ID, t1._CODE , ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t1.ID) AS rn FROM t1 ) s1 PIVOT ( max(s1._CODE) FOR s1.rn IN ([1],[2],[3],[4],[5],[6]) ) piv

结果 ：

| ID | CODE1 | CODE2 | CODE3 | CODE4 | CODE5 | CODE6 | |----|-------|-------|--------|--------|--------|--------| | 1 | abc | xyz | def | pqr | jkl | tuv | | 2 | lmn | rgb | (null) | (null) | (null) | (null) |

This can be a fairly simple PIVOT using a ROW_NUMBER() window function. I threw in a 7th CODE for ID 1 to show that it will be ignored if you're only pivoting 6 columns, however, because your OVER isn't deterministic (it can ORDER any way it wants since it only uses ID), it could pick up a different set of 6 CODEs.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE t1 ( ID int, _CODE varchar(20) ) ; INSERT INTO t1 (ID, _CODE) VALUES (1,'abc') , (1,'xyz') , (1,'def') , (1,'pqr') , (1,'jkl') , (1,'tuv') , (2,'lmn') , (2,'rgb') , (1,'ignoredOnly6') ;

Query 1:

SELECT piv.ID , piv.[1] AS CODE1 , piv.[2] AS CODE2 , piv.[3] AS CODE3 , piv.[4] AS CODE4 , piv.[5] AS CODE5 , piv.[6] AS CODE6 FROM ( SELECT t1.ID, t1._CODE , ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t1.ID) AS rn FROM t1 ) s1 PIVOT ( max(s1._CODE) FOR s1.rn IN ([1],[2],[3],[4],[5],[6]) ) piv

Results:

| ID | CODE1 | CODE2 | CODE3 | CODE4 | CODE5 | CODE6 | |----|-------|-------|--------|--------|--------|--------| | 1 | abc | xyz | def | pqr | jkl | tuv | | 2 | lmn | rgb | (null) | (null) | (null) | (null) |