如何将1列的数据拆分成3列？

Question

数据采用给定的格式-

Id      Date        Location
a123    6/6/2016    mmp
a123    6/7/2016    jpr
a123    6/8/2016    hjl
a123    6/9/2016    jhag
a678    6/10/2016   hjlwe
a678    6/11/2016   mkass
a980    6/7/2016    asdadf
a980    6/7/2016    lasdj
a980    6/7/2016    xswd

我想要给定格式的相同内容：

Id      Date 1      Location1   Date 2      Location 2  Date 3      Location 3
a123    6/6/2016    mmp         6/7/2016    jpr         6/8/2016    hjl
a678    6/10/2016   hjlwe       6/11/2016   mkass        
a980    6/7/2016    asdadf      6/7/2016    lasdj       6/7/2016 

如何在SQL中做到这一点？

Answer 1

您可以将ROW_NUMBER()与条件聚合一起使用：

SELECT s.id,
       MAX(CASE WHEN s.rnk = 1 THEN s.date END) as date_1,
       MAX(CASE WHEN s.rnk = 1 THEN s.location END) as location_1,
       MAX(CASE WHEN s.rnk = 2 THEN s.date END) as date_2,
       MAX(CASE WHEN s.rnk = 2 THEN s.location END) as location_2,
       MAX(CASE WHEN s.rnk = 3 THEN s.date END) as date_3,
       MAX(CASE WHEN s.rnk = 3 THEN s.location END) as location_3
FROM(
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY t.Date) as rnk
    FROM YourTable t) s
GROUP BY s.id

这也可以用PIVOT解决，但只要列的数量有限，我更喜欢使用条件聚合。

如果您需要添加更多的级别，只需遵循逻辑，将3替换为4，依此类推。

Answer 2

您还可以使用PIVOT (如果列数可以动态更改，则必须使用dynamic SQL)：

;WITH cte AS (
SELECT *
FROM (VALUES
('a123',    '6/6/2016',    'mmp'),
('a123',    '6/7/2016',    'jpr'),
('a123',    '6/8/2016',    'hjl'),
('a123',    '6/9/2016',   'jhag'),
('a678',    '6/10/2016',   'hjlwe'),
('a678',    '6/11/2016',   'mkass'),
('a980',    '6/7/2016',    'asdadf'),
('a980',    '6/7/2016',    'lasdj'),
('a980',    '6/7/2016',    'xswd')
) as t(Id, [Date], [Location])
)

SELECT  p1.Id,
        p1.[Date1],
        p2.[Location1],
        p1.[Date2],
        p2.[Location2],
        p1.[Date3],
        p2.[Location3],
        p1.[Date4],
        p2.[Location4]
FROM 
    (SELECT *
    FROM (
        SELECT  Id, 
                [Date], 
                'Date' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Date]) AS NVARCHAR(10)) as rn
        fROM cte
        ) AS D
    PIVOT (
    MAX([Date]) for  RN in ([Date1],[Date2],[Date3],[Date4])
    ) as pvt
    ) as p1
LEFT JOIN 
    (SELECT *
    FROM (
        SELECT  Id, 
                [Location], 
                'Location' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Date]) AS NVARCHAR(10)) as rn
        fROM cte
        ) AS D
    PIVOT (
    MAX([Location]) for  RN in ([Location1],[Location2],[Location3],[Location4])
    ) as pvt
    ) as p2
    ON p1.Id = p2.Id

输出：

Id   Date1     Location1 Date2     Location2 Date3     Location3 Date4     Location4
---- --------- --------- --------- --------- --------- --------- --------- ---------
a123 6/6/2016  mmp       6/7/2016  jpr       6/8/2016  hjl       6/9/2016  jhag
a678 6/10/2016 hjlwe     6/11/2016 mkass     NULL      NULL      NULL      NULL
a980 6/7/2016  lasdj     6/7/2016  xswd      6/7/2016  asdadf    NULL      NULL

编辑

使用动态SQL (相同的输出)：

CREATE TABLE #temp (
    Id nvarchar(10),  
    [Date] date, 
    [Location] nvarchar(10)
)

INSERT INTO #temp VALUES
('a123',    '6/6/2016',    'mmp'),
('a123',    '6/7/2016',    'jpr'),
('a123',    '6/8/2016',    'hjl'),
('a123',    '6/9/2016',   'jhag'),
('a678',    '6/10/2016',   'hjlwe'),
('a678',    '6/11/2016',   'mkass'),
('a980',    '6/7/2016',    'asdadf'),
('a980',    '6/7/2016',    'lasdj'),
('a980',    '6/7/2016',    'xswd')

DECLARE @locs nvarchar(max), 
        @dates nvarchar(max), 
        @cols nvarchar(max),
        @sql nvarchar(max)

SELECT @locs = STUFF((
    SELECT DISTINCT ',' + QUOTENAME('Location' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Date]) AS NVARCHAR(10)))
    FROM #temp
    FOR XML PATH('')
    ),1,1,'')

SELECT @dates = STUFF((
    SELECT DISTINCT ',' + QUOTENAME('Date' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Date]) AS NVARCHAR(10)))
    FROM #temp
    FOR XML PATH('')
    ),1,1,'')

SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME('Date' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Date]) AS NVARCHAR(10))) + 
                    ',' + QUOTENAME('Location' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Date]) AS NVARCHAR(10)))
    FROM #temp
    FOR XML PATH('')
    ),1,1,'')


SELECT @sql ='
SELECT  p1.Id,
        '+@cols+'
FROM 
    (SELECT *
    FROM (
        SELECT  Id, 
                [Date], 
                ''Date'' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Date]) AS NVARCHAR(10)) as rn
        fROM #temp
        ) AS D
    PIVOT (
    MAX([Date]) for  RN in ('+@dates+')
    ) as pvt
    ) as p1
LEFT JOIN 
    (SELECT *
    FROM (
        SELECT  Id, 
                [Location], 
                ''Location'' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Date]) AS NVARCHAR(10)) as rn
        fROM #temp
        ) AS D
    PIVOT (
    MAX([Location]) for  RN in ('+@locs+')
    ) as pvt
    ) as p2
    ON p1.Id = p2.Id'

EXECUTE sp_executesql @sql

DROP TABLE #temp