Crosstab Query with Dynamic Columns in SQL Server 2005 Up

Crosstab Query with Dynamic Columns in SQL Server 2005 up

There are two ways to perform a PIVOT static where you hard-code the values and dynamic where the columns are determined when you execute.

Even though you will want a dynamic version, sometimes it is easier to start with a static PIVOT and then work towards a dynamic one.

Static Version:

SELECT studentid, name, sex,[C], [C++], [English], [Database], [Math], total, average
from
(
select s1.studentid, name, sex, subjectname, score, total, average
from Score s1
inner join
(
select studentid, sum(score) total, avg(score) average
from score
group by studentid
) s2
on s1.studentid = s2.studentid
) x
pivot
(
min(score)
for subjectname in ([C], [C++], [English], [Database], [Math])
) p

See SQL Fiddle with demo

Now, if you do not know the values that will be transformed then you can use Dynamic SQL for this:

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(SubjectName)
from Score
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT studentid, name, sex,' + @cols + ', total, average
from
(
select s1.studentid, name, sex, subjectname, score, total, average
from Score s1
inner join
(
select studentid, sum(score) total, avg(score) average
from score
group by studentid
) s2
on s1.studentid = s2.studentid
) x
pivot
(
min(score)
for subjectname in (' + @cols + ')
) p '

execute(@query)

See SQL Fiddle with Demo

Both versions will yield the same results.

Just to round out the answer, if you do not have a PIVOT function, then you can get this result using CASE and an aggregate function:

select s1.studentid, name, sex, 
min(case when subjectname = 'C' then score end) C,
min(case when subjectname = 'C++' then score end) [C++],
min(case when subjectname = 'English' then score end) English,
min(case when subjectname = 'Database' then score end) [Database],
min(case when subjectname = 'Math' then score end) Math,
total, average
from Score s1
inner join
(
select studentid, sum(score) total, avg(score) average
from score
group by studentid
) s2
on s1.studentid = s2.studentid
group by s1.studentid, name, sex, total, average

See SQL Fiddle with Demo

I need to know how to create a crosstab query

This type of transformation is called a pivot. You did not specify what database you are using so I will provide a answers for SQL Server and MySQL.


SQL Server: If you are using SQL Server 2005+ you can implement the PIVOT function.

If you have a known number of values that you want to convert to columns then you can hard-code the query.

select typename, total, Deployed, Inventory, shipped
from
(
select count(*) over(partition by t.typename) total,
s.statusname,
t.typename
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) d
pivot
(
count(statusname)
for statusname in (Deployed, Inventory, shipped)
) piv;

See SQL Fiddle with Demo.

But if you have an unknown number of status values, then you will need to use dynamic sql to generate the list of columns at run-time.

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(statusname)
from assetstatus
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT typename, total,' + @cols + ' from
(
select count(*) over(partition by t.typename) total,
s.statusname,
t.typename
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) x
pivot
(
count(statusname)
for statusname in (' + @cols + ')
) p '

execute(@query)

See SQL Fiddle with Demo

This can also be written using an aggregate function with a case expression:

select typename,
total,
sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
select count(*) over(partition by t.typename) total,
s.statusname,
t.typename
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) d
group by typename, total

See SQL Fiddle with Demo


MySQL: This database does not have a pivot function so you will have to use the aggregate function and a CASE expression. It also does not have windowing functions, so you will have to alter the query slightly to the following:

select typename,
total,
sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
select t.typename,
(select count(*)
from assets a1
where a1.assettype = t.id
group by a1.assettype) total,
s.statusname
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) d
group by typename, total;

See SQL Fiddle with Demo

Then if you need a dynamic solution in MySQL, you will have to use a prepared statement to generate the sql string to execute:

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(CASE WHEN statusname = ''',
statusname,
''' THEN 1 else 0 END) AS `',
statusname, '`'
)
) INTO @sql
FROM assetstatus;

SET @sql
= CONCAT('SELECT typename,
total, ', @sql, '
from
(
select t.typename,
(select count(*)
from assets a1
where a1.assettype = t.id
group by a1.assettype) total,
s.statusname
from assets a
inner join assettypes t
on a.assettype = t.id
inner join assetstatus s
on a.assetstatus = s.id
) d
group by typename, total');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo.

The result is the same for all queries in both databases:

| TYPENAME | TOTAL | DEPLOYED | INVENTORY | SHIPPED |
-----------------------------------------------------
| Desktop | 2 | 1 | 1 | 0 |
| Laptop | 1 | 0 | 0 | 1 |
| Server | 1 | 1 | 0 | 0 |

SQL Dynamic Pivot Query in SSRS 2005

You really can take advantage of reporting services to leverage a column based cross tab report. Write the T-SQL in the db layer, then in the reporting layer drop and drop a cross tab report. In the column grouping just add the column you need and rs is awesome and smart enough to dynamically add all columns.

Here was my original comment :):

Can you post a screen shot of what you mean? You just need to create a cross tab report in RS, it doesnt matter how many columns you have or their names. That is the advantage, you lay it in the column group and it will dynamically add all columns irregardless of names. Need more details as to what you are asking and possibly some screen shots with arrows :).

One thing to always remember - don't make your database programming to difficult - presentation should mainly and almost always be done in the front end tool, in this case reporting services. Your database layer's code should be really simple standard T-SQL with maybe a grouping. The client will handle the presentation issues.

Add total column to cross tab pivot query

Add

, [Completed] + [Open] AS Total

to the SELECT list

SQL Server dynamic PIVOT query?

Dynamic SQL PIVOT:

create table temp
(
date datetime,
category varchar(3),
amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from
(
select date
, amount
, category
from temp
) x
pivot
(
max(amount)
for category in (' + @cols + ')
) p '

execute(@query)

drop table temp

Results:

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000 1000.00 NULL NULL
2012-02-01 00:00:00.000 NULL 500.00 800.00
2012-02-10 00:00:00.000 NULL 700.00 NULL
2012-03-01 00:00:00.000 1100.00 NULL NULL


Related Topics



Leave a reply



Submit