In a SQL Server how to change column data to a row wise format?

Question

In a SQL Server how to change column data's as a row wise format?...

For ex:

Input

Name   date
--------------
xxx    12/12/2015
xxx    11/12/2015
xxx    10/12/2015
yyy    9/12/2015

Desired output:

Name  Date
------------------------------------------
xxx   10/12/2015  11/12/2015  12/12/2015
yyy   9/12/2015

So, you want a single date column with dates separated by two spaces in ascending order? — Kritner, Jun 15 '15 at 13:24
Normally you need to do this work on the client. In MySql, you can use the non-standard group_concat() method. — Joel Coehoorn, Jun 15 '15 at 13:28
yes we already tried but which is defined columns, our requirement is name column and date itself, the result have to come single name and side by side date column value\ — Ethirajram, Jun 15 '15 at 13:33
@marc_s the question is not duplicate of the given one as it is about simple aggregate concatenation . — gotqn, Jun 15 '15 at 13:43

gotqn · Answer 1 · 2015-06-15T13:40:39.763

0

Check this:

DECLARE @DataSource TABLE
(
    [name] CHAR(3)
   ,[date] DATE
);

INSERT INTO @DataSource ([name], [date])
VALUES ('xxx', '12/12/2015')
      ,('xxx', '11/12/2015')
      ,('xxx', '10/12/2015')
      ,('yyy', '9/12/2015');

WITH DataSource AS
(
    SELECT DISTINCT [name]
    FROM @DataSource
)
SELECT [name]
      ,STUFF
       (
            (
                SELECT ' ' + CAST([date] AS VARCHAR(12)) 
                FROM @DataSource
                WHERE DS.[name] = [name]
                ORDER BY [date] ASC
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
            ,1
            ,1
            ,''
        )
FROM DataSource DS;

enter image description here

edited Jun 15 '15 at 13:40

answered Jun 15 '15 at 13:33

gotqn

42,737
46
157
243

Gotgn yes we got result from your query thank you very much for response..., but we have table in DB so need to declare table, how to retrieve result from directly from table with select query statement – Ethirajram Jun 15 '15 at 13:39
You can check this link - http://www.sql-server-helper.com/tips/date-formats.aspx in order to represent the date in different format. – gotqn Jun 15 '15 at 13:40
@Ethirajram Do not declare new table, just replace the `@DataSource` with your real table name in the `WITH` clause and it will be fine. – gotqn Jun 15 '15 at 13:47
yes Gotgn thank you.., – Ethirajram Jun 15 '15 at 13:50

In a SQL Server how to change column data to a row wise format?

1 Answers1