How to display the data column-wise

Question

How to display the date by column wise

Table1

ID Date Value

001 01/01/2012 100
001 02/01/2012 200
...
...
001 31/01/2012 250
002 01/01/2012 050
002 02/01/2012 100
...
002 31/01/2012 075
....

I want to display the value row by date wise row group by id riw

Expected output

ID 01/01/2012 02/01/2012 ... 31/01/2012

001  100 200 .... 250
002  050 100 .... 075
.....

How to do this with a SQL query?

bitoshi.n · Accepted Answer · 2012-05-23T10:41:51.603

1

Try this

DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)

SELECT  @cols = STUFF(( SELECT DISTINCT 
                                '],[' + `Date`
                        FROM    Table1
                        ORDER BY '],[' + `Date` FOR XML PATH('')
                      ), 1, 2, '') + ']';


SET @query = N'SELECT ID, '+
@cols +'
FROM
(SELECT  ID
FROM    Table1
PIVOT
(
SUM([`Value`])
FOR `Date` IN
( '+
@cols +' )
) AS pvt
ORDER BY ID;'


EXECUTE(@query)

SQL2000 version

DECLARE @date Date,@sql nvarchar(MAX)
DECLARE date_cursor CURSOR
  FOR SELECT DISTINCT [Date] FROM table_one ORDER BY [Date]
  SET @sql = ''
  OPEN date_cursor
  FETCH NEXT FROM date_cursor INTO @date
  WHILE @@FETCH_STATUS = 0
     BEGIN
         SET @sql = @sql+ ',MAX(CASE CONVERT(nvarchar(10),[Date],103) WHEN '''+CONVERT(nvarchar(10),@date,103)+''' THEN [Value] END) AS ['+CONVERT(nvarchar(10),@date,103)+']'
         FETCH NEXT FROM date_cursor INTO @date
     END 
CLOSE date_cursor
DEALLOCATE date_cursor

EXEC('SELECT ID'+@sql+' FROM table_one GROUP BY ID')

Or

DECLARE @loop int,@date Date,@sql nvarchar(4000)
DECLARE @TempTable TABLE
(
  [Date] DATE
)
INSERT INTO @TempTable SELECT DISTINCT [Date] FROM table1 ORDER BY [Date]
  SET @sql = ''
  SET @loop = 1
  WHILE (@loop<=31)
    BEGIN
        IF EXISTS(SELECT * FROM @TempTable WHERE DAY([Date])=@loop) 
        BEGIN
            SET @date = (SELECT [Date] FROM @TempTable WHERE DAY([Date])=@loop)
            SET @sql = @sql+ ',MAX(CASE CONVERT(nvarchar(10),[Date],103) WHEN '''+CONVERT(nvarchar(10),@date,103)+''' THEN [Value] END) AS [DATE'+CONVERT(nvarchar(2),@loop)+']'
         END
         ELSE
            SET @sql = @sql+ ', NULL AS [DATE'+CONVERT(nvarchar(2),@loop)+']'
         SET @loop = @loop+1
    END 

EXEC('SELECT ID'+@sql+' FROM table1 GROUP BY ID')

edited May 23 '12 at 10:41

answered May 12 '12 at 11:04

bitoshi.n

2,278
1
16
16

@JetJack Ok, i have edited my post. [The result can you check here](http://sqlfiddle.com/#!3/99a7c/17) – bitoshi.n May 19 '12 at 10:59
Thanks for your answer, when i exeute this line "EXEC('SELECT ID'+@sql+' FROM table_one GROUP BY ID')"getting error as "Incorrect syntax near the keyword 'FROM', Apart from this error everything is ok, plz... – JetJack May 21 '12 at 08:10
@JetJack It may because there are something wrong in build `@sql`. Try to get query by `SELECT 'SELECT ID'+@sql+' FROM table_one GROUP BY ID'`. And then, copy and run the query that you get, Hope you'll get the problem. – bitoshi.n May 21 '12 at 08:18
While i run this query like this "Select ('SELECT ID '+@sql+' FROM table_one GROUP BY ID')" Showing output as "SELECT ID , MAX(CASE CONVERT(nvarchar(10),[Date],103) WHEN '01/01/2012' THEN [Value] END) AS [01/01/2012], MAX FROM table_one GROUP BY ID" – JetJack May 21 '12 at 08:24
@JetJack `SELECT ID , MAX(CASE CONVERT(nvarchar(10),[Date],103) WHEN '01/01/2012' THEN [Value] END) AS [01/01/2012], MAX FROM table_one GROUP BY ID` is wrong query from @sql. It may because of variable length. @sql did not give you complete query that you wanted. – bitoshi.n May 21 '12 at 08:35
@JetJack Try `DECLARE @date Date,@sql nvarchar(4000)` – bitoshi.n May 21 '12 at 08:44
And one more thing, I want to enter this values in sql table (table name as table1), table1 having 32 field names(id, date1, date2... date31), but i have 3 record only (01/01/2012, 02/01/2012, 31/01/2012), When i try to insert you query values into table1 i am getting error as "number of column supplied does not match" – JetJack May 21 '12 at 09:21
@JetJack If your data is complete from date1 to date31, it will not give you error. Or, you have to write fix query `SELECT ID , MAX(CASE CONVERT(nvarchar(10),[Date],103) WHEN '01/01/2012' THEN [Value] END) AS [01/01/2012], ..., MAX(CASE CONVERT(nvarchar(10),[Date],103) WHEN '31/01/2012' THEN [Value] END) AS [31/01/2012] FROM table_one GROUP BY ID` – bitoshi.n May 21 '12 at 09:30
I have the datebetween 01 to 20 dates, remaining 11 dates are not available, so i want to insert null values for next 11 dates, how to do this, i cannot enter dates column manually because dates column value change depends on month.... – JetJack May 21 '12 at 09:50
You query is working, but it is showing only id column, it is not showing value and date column, can you please check that.... – JetJack May 23 '12 at 10:04
one more help, how can i find the days, because in my table i have 2 column for values, if it is sunday then i have to pick up the value from value2 column otherwise value1 column. Plz............ – JetJack May 23 '12 at 11:41
@JetJack may you can open new question. If i open it, i will try to help. – bitoshi.n May 23 '12 at 11:44

How to display the data column-wise

1 Answers1