Crosstab Query with Dynamic Columns in SQL Server 2008
The query you will need to get the results in your question is:
create table StudentResults(StudentID int,Name nvarchar(50),Course nvarchar(50), CourseLevel nvarchar(10));
insert into StudentResults values(1,'John','English','E2'),(1,'John','Maths','E3'),(1,'John','Computing','L2');
select StudentID
,Name
,[Computing]
,[Maths]
,[English]
from StudentResults
pivot(max(CourseLevel) for Course in([Computing],[Maths],[English])
) as p;
Output:
StudentID Name Computing Maths English
1 John L2 E3 E2
Though as you may be able to work out, this requires hard coding the subjects. If your list of subjects is likely to change, then this query will no longer be fit for purpose.
If you are comfortable, you can remedy this with dynamic SQL:
declare @cols as nvarchar(max)
,@query as nvarchar(max);
set @cols = stuff(
(select distinct ','+quotename(Course)
from StudentResults
for xml path(''),type).value('.','nvarchar(max)'
)
,1,1,''
);
set @query = 'select StudentID
,Name
,'+@cols+'
from StudentResults
pivot (max(CourseLevel) for Course in ('+@cols+')
) p';
execute (@query);
Ideally though, you would simply return a set of data, as it appears to be in your source table and let your reporting layer (SSRS for example) handle the pivoting, which it is much better suited towards than pure SQL.
Crosstab query in SQL Server 2008
I would query it like
select
Date,
Sum(case when Calendar = 'Canada' then Days else 0 end) as Canada,
Sum(case when Calendar = 'Europe' then Days else 0 end) as Europe,
Sum(case when Calendar = 'UK' then Days else 0 end) as UK,
Sum(case when Calendar = 'US' then Days else 0 end) as Us,
Sum(case when Calendar not in ('Canada','Europe','UK','US') then Days else 0 end)
as Other
from
MyTable
group by
Date
Crosstab query with count of values in SQL Server 2008 R2
There are a few different ways that you can convert the rows to columns. One way that you can do this is by using an aggregate function with a CASE expression:
select ApproachStatus,
sum(case when Clinic = 'GI Med Onc' then 1 else 0 end) [GI Med Onc],
sum(case when Clinic = 'Breast Med Onc' then 1 else 0 end) [Breast Med Onc]
from yt
group by ApproachStatus;
See SQL Fiddle with Demo
Or since you are using SQL Server 2005+, you can use the PIVOT function:
select ApproachStatus, [GI Med Onc],[Breast Med Onc]
from yt
pivot
(
count(Clinic)
for Clinic in ([GI Med Onc],[Breast Med Onc])
) piv;
See SQL Fiddle with Demo.
If you have an unknown Clinic values, then you will need to look at using dynamic SQL to get the result:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Clinic)
from yt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ApproachStatus,' + @cols + '
from yt
pivot
(
count(Clinic)
for Clinic in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo. All queries will give a result:
| APPROACHSTATUS | GI MED ONC | BREAST MED ONC |
------------------------------------------------
| Approached | 2 | 1 |
| Not Approached | 0 | 1 |
| Pending | 1 | 0 |
Sql Server 2008 Cross Tab Query
You should be able to do this with the 'pivot' operator. Something like this (though I am sure I muffed some spelling or syntax details...):
select catTitle, [1] as site1, [2] as site2, [3] as site3, [4] as site4, [5] as site5
from (select category.catTitle, equipment.quantity, site.title
from equipment
inner join site
on (equipment.siteid = site.siteid)
inner join category
on (category.catid = equipment.catid)
)
pivot
(
sum (quantity)
for equipment.siteid in ( [1], [2], [3], [4], [5] )
) as pvt
order by pvt.category;
The problem with this is that you need to know the exact set of site ids you want to include in the query. If you need a more dynamic crosstab (like you can get in Excel), then you need to generate the query text as a string and use sp_executesql to run it. In the generated text, you include as many of the "[1], [2], [3], [4], [5]..." and "[1] as site1, [2] as site2..." things as you need.
Sql Cross Tab (Query)
I tested the below with your provided sample data and it returns accordingly. Basically for each column you want to conditionally pass in the TotalPatients tot he SUM aggregator based on the PatientType value.
select
Dept_Name
,sum(case when Patient_Type = 'Entitled' then TotalPatients ELSE 0 end) Entitled
,sum(case when Patient_Type = 'General' then TotalPatients ELSE 0 end) General
,sum(case when Patient_Type = 'Staff' then TotalPatients ELSE 0 end) Staff
,sum(case when Patient_Type = 'Referred' then TotalPatients ELSE 0 end) Referred
from @t
group by Dept_Name
In the cases where you need dynamic columns...
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ( 'U' )
AND o.id = Object_id(N'tempdb..#t'))
DROP TABLE #t;
CREATE TABLE #t
(
dept_name NVARCHAR(255),
patient_type NVARCHAR(255),
totalpatients INT
)
INSERT INTO #t
(dept_name,
patient_type,
totalpatients)
Insert Into #T (Dept_Name, Patient_Type , TotalPatients)
SELECT department.dept_name,
patient.patient_type,
Count(*) AS TotalPatients
FROM patient
INNER JOIN payment
ON patient.regno = payment.regno
INNER JOIN department
ON payment.deptid = department.dept_id
WHERE ( CONVERT(VARCHAR, patient.regdatetime, 112) =
CONVERT(VARCHAR, Getdate() - 30, 112) )
GROUP BY department.dept_name,
patient.patient_type
DECLARE @cols AS NVARCHAR(max),
@query AS NVARCHAR(max)
SELECT @cols = Stuff((SELECT DISTINCT ',' + Quotename(patient_type)
FROM #t
GROUP BY dept_name,
patient_type
ORDER BY 1
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1,
'')
SET @query = N'SELECT Dept_Name, ' + @cols + N'
from
#T x
pivot
(
SUM(TotalPatients)
for Patient_Type in ('
+ @cols + N')
) p '
EXEC Sp_executesql @query;
Cross Tab query in SQL Server 2008
In SQL Server you will want to look at the PIVOT function. Considering you did not provide many details on your table or sample data, based on your attempt in the comments something like this might point you in the right direction.
SELECT *
FROM
(
SELECT Equipt, Shed
FROM PunctualityMain
) x
PIVOT
(
COUNT(Equipt)
FOR Shed IN ([BSL], [AQ])
) p
If you post some more details, then we could provide additional help.
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 |
CrossTab Query / Pivot Table in MS SQL?
Something like this might help:
Query 1:
SELECT
terminal,
count(CASE WHEN load_time_mns >= 0 AND load_time_mns < 1 THEN 1 END) [0-1 mns],
count(CASE WHEN load_time_mns >= 1 AND load_time_mns < 2 THEN 1 END) [1-2 mns],
count(CASE WHEN load_time_mns >= 2 AND load_time_mns < 3 THEN 1 END) [2-3 mns]
FROM t
GROUP BY terminal
Results:
| TERMINAL | 0-1 MNS | 1-2 MNS | 2-3 MNS |
|------------|---------|---------|---------|
| Terminal 1 | 0 | 1 | 0 |
| Terminal 2 | 0 | 0 | 0 |
| Terminal 3 | 0 | 0 | 1 |
| Terminal 6 | 0 | 1 | 0 |
Fiddle here.
Note that in your example you did not include 1 in the [0-1] range but you did include 3 in the [0-3] range, which seems not right.
SQL Server 2008 CrossTab equivalent
There are a few different ways that you can do this in SQL Server you can use the PIVOT function:
select userid,
[12] moduleNum12,
[10] moduleNum10,
[18] moduleNum18
from
(
select userid, moduleid, cast(passed as int) passed
from yourtable
) d
pivot
(
max(passed)
for moduleId in ([12], [10], [18])
) piv;
See Demo
Or you can use an aggregate function with a CASE expression:
select userid,
max(case when moduleid = 12 then cast(passed as int) end) moduleNum12,
max(case when moduleid = 10 then cast(passed as int) end) moduleNum10,
max(case when moduleid = 18 then cast(passed as int) end) moduleNum18
from yourtable
group by userid;
See Demo.
The above work great if the values are known nut if you have unknown values, then you will need to use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@colsAlias AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ModuleID)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsAlias = STUFF((SELECT distinct ', ' + QUOTENAME(ModuleID) +' as moduleNum'+cast(ModuleID as varchar(10))
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT userid,' + @colsAlias + '
from
(
select userid, moduleid, cast(passed as int) passed
from yourtable
) d
pivot
(
max(passed)
for moduleid in (' + @cols + ')
) p '
execute(@query)
See Demo
A peculiar crosstab query in SQL Server
Assuming you need DYNAMIC
Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Color) From YourTable Order by 1 For XML Path('')),1,1,'')
Select @SQL = '
Select [Package Barcode],[Total],' + @SQL + '
From (
Select [Package Barcode],B.[Color],B.[Cnt]
From YourTable A
Cross Apply (
Select Color=A.Color,Cnt=1
Union All
Select Color=''Total'',Cnt=1
) B
) A
Pivot (Sum(Cnt) For [Color] in ([Total],' + @SQL + ') ) p'
Exec(@SQL);
Returns
Package Barcode Total BLUE PINK RED YELL
12345 3 1 NULL 1 1
19999 5 2 1 2 NULL
EDIT - If it helps, the SQL Generates looks like this
Select [Package Barcode],[Total],[BLUE],[PINK],[RED],[YELL]
From (
Select [Package Barcode],B.[Color],B.[Cnt]
From YourTable A
Cross Apply (
Select Color=A.Color,Cnt=1
Union All
Select Color='Total',Cnt=1
) B
) A
Pivot (Sum(Cnt) For [Color] in ([Total],[BLUE],[PINK],[RED],[YELL]) ) p
EDIT 2
Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Color) From YourTable Order by 1 For XML Path('')),1,1,'')
Select @SQL = '
Select [Package Barcode],[Total],' + @SQL + '
From (
Select [Package Barcode],[Color],[Cnt]=case when Sum(Cnt)=0 then ''>>'' else cast(Sum(Cnt) as nvarchar(25)) end
From (
Select [Package Barcode],[Color],[Cnt]=Sum(1) from YourTable Group By [Package Barcode],[Color]
Union ALL
Select Distinct [Package Barcode],C.[Color],[Cnt]=0
From YourTable
Cross Join (Select Distinct Color From YourTable) C
Union All
Select [Package Barcode],[Color]=''Total'',[Cnt]=Sum(1) from YourTable Group By [Package Barcode]
) A
Group By [Package Barcode],[Color]
) A
Pivot (max(Cnt) For [Color] in ([Total],' + @SQL + ') ) p'
Exec(@SQL);
Returns
Package Barcode Total BLUE PINK RED YELL
12345 3 1 >> 1 1
19999 5 2 1 2 >>
Having trouble getting → to display, so I put a >> as a place marker.
Edit 3 - Conditional Aggregation
Select [Package Barcode]
,[Total] = sum(1)
,[BLUE] = sum(case when color='Blue' then 1 else 0 end)
,[Pink] = sum(case when color='Pink' then 1 else 0 end)
--.. Add more colors
From YourTable
Group By [Package Barcode]
Related Topics
Execution Order of Conditions in SQL 'Where' Clause
Database Normalization - Who's Right
SQL - Remove the Duplicate Results
How to Alter a Table for Identity Specification Is Identity SQL Server
Combine Consecutive Date Ranges
Spring Boot Query Annotation with Nativequery Doesn't Work in Postgresql
Split String by Comma in SQL Server 2008
Postgresql Does Not Use a Partial Index
Oracle Connect by Clause Equivalent in SQL Server
Compare 3 Consecutive Rows in a Table
Increase Ms Access Insert Performance
SQL - Select Rows from Two Different Tables
Show Create Table Tablename in SQL Server