none
How to convert Rows into Columns using T-SQL Server 2005 ?

    Question

  • Hi friends, i need to convert Rows into columns in query for SSRS 2005 report.

    below is my exisitng query Results sets.

    Year                Month                       Count

    2006                   1                            100

    2006                   1                            124

    2006                   1                            150

    2006                   2                            635

    2006                   2                            439

    2006                   2                            102

    2006                   3                            435

    2006                   3                            11.25

    2006                    3                            489  

    For the SSRS 2005, I need below Result Sets.

         2006-01                                 2006-02                                              2006-03

          100                                         635                                                      435

          124                                         439                                                      11.25

          150                                         102                                                      489

    How, can i handle and managed this on T-SQL Side 2005 ? any other best alternative ?

    Let me know.

    Thanks.

    Monday, May 03, 2010 11:22 PM

Answers

All replies

  • Do you know years and months in advance? If yes, then

    select * from (select row_number() over (partition by Year, Month order by Count) as row, cast(year as char(4)) +'-' +  right('00' + cast(Month as varchar(2)),2) as YearMonth, Count from myTable) src PIVOT (sum(Count) for YearMonth in ([2006-01],[2006-02],[2006-03])) pvt

    If years and months are not known in advance, then you need to use Dynamic Pivot. Simply search this forum for examples.

    One sample is here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/41310b75-e227-4b22-b1ac-d06cf0f685e7/#cd376292-5ba2-4f01-b339-5ff2fe11b376


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 04, 2010 12:06 AM
  • My query considering Last 13 Months, that i know.

    How can i manage ?

     

    Pls help me.

    Tuesday, May 04, 2010 12:17 AM
  • You may need to use dynamic SQL.

    declare @StartMonthYear datetime, @Columns nvarchar(max), @SQL nvarchar(max)

    set @StartMonthYear = dateadd(month, datediff(month, 0,getdate())-13,0)

    set @Columns = ''

    select @Columns = @Columns + ',' + quotename(cast([year] as char(4)) +'-' +  right('00' + cast([Month] as varchar(2)),2))

    set @Columns = stuff(@Columns,1,1,'')

    from myTable where cast (cast([year] as char(4)) +  right('00' + cast([Month] as varchar(2)) + '01' as datetime) >=@StartMonthYear

    print @Columns -- for debug purpose

    set @SQL = N'select * from (select row_number() over (partition by Year, Month order by Count) as row, cast(year as char(4)) +'-' +  right('00' + cast(Month as varchar(2)),2) as YearMonth, Count from myTable) src PIVOT (sum(Count) for YearMonth in (' + @Columns + ')) pvt'

    print @SQL -- for debug purpose

    execute (@SQL)

    ---------------------

    The above is from the top of my head - you may need to adjust.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 04, 2010 12:35 AM
  • You don't really need to use Dynamic SQL if always required 13 month column. Just find the different in month with reference to your starting date and PIVOT it

    declare  @StartDate  datetime
    
    select  @StartDate  = '2006-01-01'
    
    ; with result as
    (
      select  [Year] = 2006, [Month] = 1, [Count] = 100  union all
      select  [Year] = 2006, [Month] = 1, [Count] = 124  union all
      select  [Year] = 2006, [Month] = 1, [Count] = 150  union all
      select  [Year] = 2006, [Month] = 2, [Count] = 635  union all
      select  [Year] = 2006, [Month] = 2, [Count] = 439  union all
      select  [Year] = 2006, [Month] = 2, [Count] = 102  union all
      select  [Year] = 2006, [Month] = 2, [Count] = 101
    ),
    data
    as
    (
      select  DteNo  = datediff(month,
            @StartDate,
            dateadd(month, [Month] - 1, dateadd(year, [Year] - 1900, 0))) + 1,
        [Count],
        ColNo   = row_number() over (partition by dateadd(month, [Month] - 1, dateadd(year, [Year] - 1900, 0))
                 order by [Count])
      from  result
    )
    select  [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13]
    from  data d
      pivot
      (
        sum([Count])
        for DteNo in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13])
      ) p


    KH Tan
    Tuesday, May 04, 2010 12:57 AM
  • What about dynamic column names? 
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 04, 2010 1:11 AM
  • that can be handle easily at the front end. Just add 1 month to the starting date
    KH Tan
    Tuesday, May 04, 2010 1:14 AM
  • SELECT SUM([2006-01]) [2006-01],SUM([2006-02]) [2006-02],SUM([2006-03]) [2006-03] FROM ( SELECT VAL '2006-01',0 '2006-02',0 '2006-03' FROM YMC WHERE YEAR=2006 AND MONTH=1 UNION ALL SELECT 0 '2006-01',VAL '2006-02',0 '2006-03' FROM YMC WHERE YEAR=2006 AND MONTH=2 UNION ALL SELECT 0 '2006-01',0 '2006-02',Val '2006-03' FROM YMC WHERE YEAR=2006 AND MONTH=3 ) RES YOU CAN REPLACE SUM TO MIN OR MAX
    Tuesday, May 04, 2010 5:50 AM
  • Thanks for replying.

    I have Column Chart.

    For some of group category, Data Values representing in Bars and for one group values are in Line Chart.

    actually, i took Vales 1 and Values 2 for Bar and Line in char and based on group valeu , i hide the values.

    But, in the Legend, it showing me Legends for All twice.

    let me know.

    Thanks.

    Wednesday, May 05, 2010 11:24 PM
  • Thanks for replying.

    I have Column Chart.

    For some of group category, Data Values representing in Bars and for one group values are in Line Chart.

    actually, i took Vales 1 and Values 2 for Bar and Line in char and based on group valeu , i hide the values.

    But, in the Legend, it showing me Legends for All twice.

    let me know.

    Thanks.

    Wednesday, May 05, 2010 11:24 PM
  • Hi friends, i need to convert Rows into columns in query for SSRS 2005 report.


    This is what SSRS matrix (crosstab) report does automatically.

    Just run Report Wizard and select Matrix report.

    SSRS Videos: http://www.youtube.com/watch?v=_93nW22NOkg

    http://www.youtube.com/watch?v=M9JJ8mtLpSo

    Consider asking your SSRS questions  on SQL Server Reporting Services forum.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Thursday, May 06, 2010 2:04 AM
  • Hello,

    For this you need to change the query and the design in the report too.

    Here is the query part: The query should look like below.

    DECLARE @TABLE TABLE (Year INT, Month INT, Count FLOAT)
    
    INSERT INTO @TABLE VALUES (2006,1,100)
    INSERT INTO @TABLE VALUES (2006,1,124)
    INSERT INTO @TABLE VALUES (2006,1,150)
    INSERT INTO @TABLE VALUES (2006,2,635)
    INSERT INTO @TABLE VALUES (2006,2,439)
    INSERT INTO @TABLE VALUES (2006,2,102)
    INSERT INTO @TABLE VALUES (2006,3,435)
    INSERT INTO @TABLE VALUES (2006,3,11.25)
    INSERT INTO @TABLE VALUES (2006,3,489)
    
    
    SELECT ROW_NUMBER() OVER(PARTITION BY Month ORDER BY Year, Month ) AS RGrp
     , CAST(Year AS NVARCHAR) + '-' + CAST('0' + CAST(Month AS NVARCHAR) AS NVARCHAR(2)) AS CGrp
     , Count AS Val
    FROM @TABLE
    

    Coming to that of the report design:

    1. Place a matrix on the report design.
    2. Column grouping field should be CGrp
    3. Row grouping field should be RGrp.
    4. Details field should be Val

    Hope its clear & helpful...


    Pavan Kokkula Infosys Technologies Limited.
    Thursday, May 06, 2010 4:31 AM
  • select
    max(case Month when 1 then Count end),
    max(case Month when 2 then Count end),
    max(case Month when 3 then Count end),
     from (select year,month,Count,
        Row_Number() over(partition by month order by Count) as rn
         from t) a
    group by year,rn
    order by year,rn;
    

    maybe

    • Proposed as answer by Aketi Jyuuzou Thursday, May 06, 2010 10:41 AM
    Thursday, May 06, 2010 10:41 AM
  • SQLUSA is right on pointing to advanced pivot table or matrix control of Reporting Services.

    Actually working with dynamic pivot tables is not the perfect solution in t-sql for such requirements.

    In Reporting Services, a great solution is developed by Microsoft.


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    Thursday, May 06, 2010 10:46 AM
  • Thanks for the reply. I marked as A Answer.

    he below i have  naother problem.

    pls let me know.

     

    I created a simple Column chart (without gridline) in the SSRS 2005  report, however, the chartplot area always has 4 lines: X Axis line, Y Axis Line, top border line and right border line to surround the plot area. Is there any way that we can hide the top border and right border line? I have tried to set the Border line style to None, but it hide the X Axis line and Y Axis Line as well.

    I want to show line as a X-axis and Y-axis only, Opposite line of Both axis i want to hide.

    let me know any suggestions / ideas.

    Thanks.

    Thursday, May 06, 2010 9:27 PM