CODEWITHSUNDEEP
sqlsql-server
Anuj Yadav
Anuj Yadav

Reputation: 39

How to split data of 1 column in 3 columns?

Data is in given format-

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

I want the same in given format-:

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

How to do that in SQL?

Upvotes: 2

Views: 114

Answers (3)

gofr1
gofr1

Reputation: 15977

Also you can make it with PIVOT (if number of columns can change dynamically you must use 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

Output:

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

EDIT

With dynamic SQL (same output):

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

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

If you don't know how many columns you have, you can use a dynamic version of sagi's answer. Here is one using a dynamic crosstab:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql =
'SELECT
    t.id' + CHAR(10);

SELECT @sql = @sql + 
(SELECT
'   , MAX(CASE WHEN rn = ' + CAST(rn AS VARCHAR(10)) + ' THEN t.Date END) AS ' + QUOTENAME('Date' + CAST(rn AS VARCHAR(10))) + CHAR(10) +
'   , MAX(CASE WHEN rn = ' + CAST(rn AS VARCHAR(10)) + ' THEN t.Location END) AS ' + QUOTENAME('Location' + CAST(rn AS VARCHAR(10))) + CHAR(10)
FROM (
    SELECT DISTINCT 
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY(SELECT NULL)) AS rn
    FROM tbl
) t
ORDER BY rn
FOR XML PATH(''));

SELECT @sql = @sql +
'FROM (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Date) AS rn
    FROM tbl
) t
GROUP BY t.Id;';

PRINT (@sql);
EXEC (@sql);

ONLINE DEMO

Upvotes: 0

sagi
sagi

Reputation: 40471

You can use ROW_NUMBER() with conditional aggregation :

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

This can also be solved with PIVOT but I prefer to use conditional aggregation as long as the amount of the columns is limited .

If you need to add more levels, just follow the logic and replace 3 with 4 and so on..

Upvotes: 2

Related Questions