none
Convert Rows into columns

    Question

  • I got a table values as bellow.
    CREATE TABLE #temp3 (ID INT,Date DATETIME,totalcount INT)
    
    id		date 	totalcount
    5	2011-07-30 	2
    5	2011-07-16 	1
    5	2011-07-23 	2
    77	2011-07-16 	1
    77	2011-08-28 	1
    77	2011-07-10 	1
    152	2011-07-03 	1
    
    And I want the output as 
    
    id 2011-07-30 	2011-07-16 2011-07-23 2011-08-28 2011-07-10 2011-07-03
    5		2			0			0		 0				0			0
    77		0		 1			0		 1    1   0
    152		0			0			0		 0				0			1
    


    FYI: The rows in #temp3 are dynamic. The dates are not static.

    I am using Microsoft SQL Server  2000 - (Build 3790: Service Pack 2)

     

    Please can anyone help me?


    Thank You, Naveen email:naveen_dkt@yahoo.co.in
    • Edited by naveendkt Tuesday, August 30, 2011 5:47 PM
    Tuesday, August 30, 2011 5:32 PM

Answers

  • You may want to check this:

    Create Table #temp3 (ID INT,Date_ DATETIME,totalcount INT)
    Insert Into #temp3 
    Select 5, '2011-07-30', 2 Union All 
    Select 5, '2011-07-16', 1 Union All 
    Select 5, '2011-07-23', 2 Union All 
    Select 77, '2011-07-16', 1 Union All 
    Select 77, '2011-08-28', 1 Union All 
    Select 77, '2011-07-10', 1 Union All 
    Select 152, '2011-07-03', 1
    
    Declare @myDates Varchar(1000), @SelectedDate DateTime 
    Set @myDates = ''
    Set @SelectedDate = '01/01/1900'
    
    While @SelectedDate <> '01/01/2050' 
    Begin 
    	Set @SelectedDate = IsNull((Select Distinct top 1 Date_ From #temp3 Where Date_ > @SelectedDate Order by Date_), '01/01/2050')
    	If @SelectedDate <> '01/01/2050'
    	Begin 
    		Set @myDates = @myDates + 'Sum(Case When Date_ = ' + '''' + Convert(Varchar(10), @SelectedDate, 101) + '''' + ' Then TotalCount Else 0 End)' + ' As [' + CONVERT(Varchar(20), @SelectedDate, 101) + '],'
    	End 
    End 
    Set @myDates = 'Select ID, ' + SubString(@myDates, 1, Len(@myDates) - 1) + ' From #temp3 Group By ID' 
    Execute @myDates 
    
    

     


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by naveendkt Tuesday, August 30, 2011 7:47 PM
    Tuesday, August 30, 2011 6:48 PM

All replies

  • You need to use PIVOT operator for this,

    SELECT * 
    FROM 
    (SELECT ID, CONVERT(VARCHAR(10), Date,121) Date, totalcount
    FROM #temp3 ) tbl
    PIVOT (MIN(totalcount) FOR Date IN ([2011-07-03],[2011-07-10],[2011-07-16],[2011-07-23],[2011-07-30],[2011-08-28])) Pvt
    
    

     


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, August 30, 2011 5:40 PM
  • Thanks dbp2784 for quick reply..

     

      I tried to use PIVOT but I am getting a error "Incorrect syntax near 'PIVOT'."  Is PIVOT supported in Microsoft SQL Server  2000? I am not sure about this..


    Thank You, Naveen email:naveen_dkt@yahoo.co.in
    Tuesday, August 30, 2011 5:47 PM
  • PIVOT operator is not supported in SQL 2000, You need to use a different method.

    SELECT ID,
    SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-03' THEN TotalCount else 0 END) AS '2011-07-03',
    SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-10' THEN TotalCount else 0 END) AS '2011-07-10',
    SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-16' THEN TotalCount else 0 END) AS '2011-07-16',
    SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-23' THEN TotalCount else 0 END) AS '2011-07-23',
    SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-30' THEN TotalCount else 0 END) AS '2011-07-30',
    SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-08-28' THEN TotalCount else 0 END) AS '2011-08-28'
    FROM #temp3 
    GROUP BY ID
    
    


     


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, August 30, 2011 6:05 PM
  • dbp2784 I can't write as you mentioned. Because as I told earlier the dates are dynamic.They are not static.

    Any other options?

     


    Thank You, Naveen email:naveen_dkt@yahoo.co.in
    Tuesday, August 30, 2011 6:12 PM
  • For dynamic dates you obviously need dynamic query. With dynamic PIVOT it's a piece of cake, with dynamic case based query it's slightly more complex - you need to know how to construct this dynamic query.

    Take a look at my blog showing several examples of dynamic pivot. I show a bit more complex problem, but your case should fit

     

    Dynamic PIVOT on multiple columns

    Update. I reviewed the code and I see I used XML PATH and ROW_NUMBER() function that are part of the SQL Server 2005+

    In any case, the idea remains the same and for your case you may want to google on

    'DYNAMIC CROSS TAB SQL Server 2000'


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 30, 2011 6:18 PM
    Moderator
  • Thanks Naomi..

    But I can't use PIVOT as my DB is in MSSQL 2000.I came to know PIVOT is introduced in MSSQL 2005.

    I looked at the DB property and found compatibility level is 80. I think it will only work in compatibility level 90 and 100


    Thank You, Naveen email:naveen_dkt@yahoo.co.in
    Tuesday, August 30, 2011 6:25 PM
  • Please read carefully what I wrote. For your case you need dynamic case based aggregation. Say, quick search on the above search terms brings

    http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

    I suggest to go from there.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, August 30, 2011 6:48 PM
    Moderator
  • You may want to check this:

    Create Table #temp3 (ID INT,Date_ DATETIME,totalcount INT)
    Insert Into #temp3 
    Select 5, '2011-07-30', 2 Union All 
    Select 5, '2011-07-16', 1 Union All 
    Select 5, '2011-07-23', 2 Union All 
    Select 77, '2011-07-16', 1 Union All 
    Select 77, '2011-08-28', 1 Union All 
    Select 77, '2011-07-10', 1 Union All 
    Select 152, '2011-07-03', 1
    
    Declare @myDates Varchar(1000), @SelectedDate DateTime 
    Set @myDates = ''
    Set @SelectedDate = '01/01/1900'
    
    While @SelectedDate <> '01/01/2050' 
    Begin 
    	Set @SelectedDate = IsNull((Select Distinct top 1 Date_ From #temp3 Where Date_ > @SelectedDate Order by Date_), '01/01/2050')
    	If @SelectedDate <> '01/01/2050'
    	Begin 
    		Set @myDates = @myDates + 'Sum(Case When Date_ = ' + '''' + Convert(Varchar(10), @SelectedDate, 101) + '''' + ' Then TotalCount Else 0 End)' + ' As [' + CONVERT(Varchar(20), @SelectedDate, 101) + '],'
    	End 
    End 
    Set @myDates = 'Select ID, ' + SubString(@myDates, 1, Len(@myDates) - 1) + ' From #temp3 Group By ID' 
    Execute @myDates 
    
    

     


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by naveendkt Tuesday, August 30, 2011 7:47 PM
    Tuesday, August 30, 2011 6:48 PM
  • Thank you all..

     

    I have generated dynamic Select statement and that solved my prob..


    Thank You, Naveen email:naveen_dkt@yahoo.co.in
    Tuesday, August 30, 2011 7:46 PM