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.

Answer 1

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

Answer 2

My query considering Last 13 Months, that i know.

How can i manage ?

 

Pls help me.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

that can be handle easily at the front end. Just add 1 month to the starting date

---

KH Tan

Answer 7

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

Answer 8

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.

Answer 9

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.

Answer 10

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

Answer 11

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.

Answer 12

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

Answer 13

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

Answer 14

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.