2

How to get column wise data in SQL Server?

Format:

Name    Date
----    -----
xxx     10/15/2015
xxx     12/15/2015
xxx     15/15/2015
yyy     20/15/2015
yyy     25/15/2015

Desired output:

Name   Date         Date         Date
--------------------------------------------
xxx    10/15/2015   12/15/2015   15/15/2015
yyy    20/15/2015   25/15/2015
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ethi Raj
  • 31
  • 1
  • 4

2 Answers2

0

You can use this code for example which will pivot the data:

On MySQL:

SELECT data.name, 
    if(data.row_number=1,date,null) as date1,
    if(data.row_number=2,date,null) as date2,
    if(data.row_number=3,date,null) as date3,
    if(data.row_number=4,date,null) as date4,
    if(data.row_number=5,date,null) as date5
FROM (
    SELECT @row_number:=@row_number+1 AS row_number, name, date 
    FROM yourTable, (SELECT @row_number:=0) AS t
    ORDER BY date 
    ) as data
GROUP BY data.name;

On SQL Server:

-- Generate demo data
CREATE TABLE #yourTable(name nvarchar(20), date date)
INSERT INTO #yourTable(name,date)
VALUES(N'xxx',GETDATE()), (N'xxx', DATEADD(day,-1,GETDATE())), (N'yyy',GETDATE()), (N'yyy', DATEADD(day,1,GETDATE()))

-- this is your part
SELECT pvt.*
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) as rn, name, date
    FROM #yourTable
) as data
PIVOT(
    MIN(date)
    FOR rn IN([1],[2],[3],[4],[5],[6])
) as pvt

-- cleanup
DROP TABLE #yourTable

This will be a dynamic pivot which will adapt if your date list will grow:

-- Generate demo data
CREATE TABLE #yourTable(name nvarchar(20), date date)
INSERT INTO #yourTable(name,date)
VALUES(N'xxx',GETDATE()), (N'xxx',DATEADD(day,1,GETDATE())), (N'xxx', DATEADD(day,-1,GETDATE())), (N'yyy',GETDATE()), (N'yyy', DATEADD(day,1,GETDATE()))

DECLARE @sql nvarchar(max), @columnlist nvarchar(max)

SELECT @columnlist = 
        COALESCE(@columnlist + N',['+CONVERT(nvarchar(max),ROW_NUMBER() OVER(ORDER BY date))+']', 
            N'['+CONVERT(nvarchar(max),ROW_NUMBER() OVER(ORDER BY date))+']'
        )
FROM #yourTable
WHERE name = (
    SELECT TOP (1) name
    FROM #yourTable
    GROUP BY name
    ORDER BY COUNT(*) DESC
)
SELECT @columnlist

-- this is your part
SET @sql = N'
SELECT pvt.*
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) as rn, name, date
    FROM #yourTable
) as data
PIVOT(
    MIN(date)
    FOR rn IN('+@columnlist+')
) as pvt'
EXEC(@sql)

-- cleanup
DROP TABLE #yourTable
Ionic
  • 3,884
  • 1
  • 12
  • 33
  • This might work for the example in the OP, but would this scale for an arbitrary number of records? – Tim Biegeleisen Jun 19 '15 at 07:59
  • No as it won't do an all other pivots (even on sql server without dynamic sql). You just need to add enough date columns. – Ionic Jun 19 '15 at 08:08
  • This is for something else than SQL Server? So many things that aren't valid SQL Server things like if, :=, order by in derived table... – James Z Jun 19 '15 at 08:17
  • Ah its for MySQL. The question was tagged before as MySQL. I provide SQL-Server. – Ionic Jun 19 '15 at 08:34
  • I've added the sql server code. But if you want it to be dynamical, I'll provide the code too. But this will just work on batches or procedures. – Ionic Jun 19 '15 at 08:38
  • ? It was already added for 38 minutes. You should take a look at the answer. – Ionic Jun 19 '15 at 09:22
0

may be this will suits your requirement 

  declare @t table (name varchar(5),dated varchar(10))
    insert into @t (name,dated)values 
    ('xxx','10/15/2015'),('xxx','12/15/2015')
    ,('yyy','15/15/2015'),('yyy','20/15/2015'),('yyy','25/15/2015')


Select name,[1]As [Date],[2]As [Date],[3]As [Date]   from (
        select name,dated,ROW_NUMBER()OVER(PARTITION BY name ORDER BY dated)RN from @t
        )T
        PIVOT(MIN(dated) FOR RN IN ([1],[2],[3]))P
mohan111
  • 8,633
  • 4
  • 28
  • 55