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 |
To build a cross tab query with details
Incorrect first answer:
TRANSFORM First(tblTeamdata.Teammemebername) AS FirstMembername
SELECT tblTeamdata.DAT
FROM tblTeamdata
GROUP BY tblTeamdata.DAT
PIVOT tblTeamdata.Teamname;
Since each cell in the cross tab can have multiple MemberCodes you will have to use a function to return a list of those names. The function shall have date and teamcode as parameters.
Function names(dat As Variant, team As Variant)
Dim res$, sql$
Dim rs As DAO.Recordset
If IsNull(dat) Or IsNull(team) Then
names = Null
Else
sql = "SELECT * FROM Teamdata"
sql = sql & " Where ScheduleDate =#" & dat & "#"
sql = sql & " AND TeamCode=""" & team & """"
sql = sql & " Order by TeamMemberCode;"
Set rs = CurrentDb.OpenRecordset(sql)
Do Until rs.EOF
If res <> "" Then res = res & ","
res = res & rs!TeamMemberCode
rs.MoveNext
Loop
rs.Close
names = res
End If
End Function
TRANSFORM names([ScheduleDate],[Teamcode]) AS Result
SELECT TeamData.ScheduleDate
FROM TeamData
GROUP BY TeamData.ScheduleDate
PIVOT TeamData.TeamCode;
The Result of the query with the above dataset will be:
ScheduleDate NT USHRL USHRT
2022-03-04 NW RN KI,KN,RF
2022-03-11 MF QD KI,KN,NW
How can I Create a Multi-Value CrossTab Query in Access 2013?
The answer was to use an inner join (similar to what I've read elsewhere). However, for those of you that don't know SQL, or couldn't figure it out -- I wrote my own SQL query from scratch that worked.
Here is a generic query for all of you that stumble upon this with a similar issue. Simply copy the code below, do a replace for the following varialbes... and you should be able to expand from there!
Variables
[myCrossTabQueryCOUNT] = name of your COUNT crosstab query
[myCrossTabQuerySUM] = name of your SUM crosstab query
[Month of Service] = field name that is the primary ROW HEADING for each crosstab query
[mySum] = field name of your 'Value' field in the [myCrossTabQuerySUM] query (in my example it held a Sum value)
[myCount] = field name of your 'Value' field in the [myCrossTabQueryCOUNT] query (in my example it held a Count value)
The SQL View Query
SELECT [myCrossTabQueryCOUNT].[Month of Service], [myCrossTabQueryCOUNT].[myCount] AS [Count], [myCrossTabQuerySUM].[mySum] AS [Total Claim Charges]
FROM (([myCrossTabQueryCOUNT]
INNER JOIN [myCrossTabQuerySUM]
ON [myCrossTabQueryCOUNT].[Month of Service]=[myCrossTabQuerySUM].[Month of Service])
ORDER BY [myCrossTabQueryCOUNT].[Month of Service];
Using A Crosstab On A Report
Simply change the IN clause to your columns. The linked article uses 1,2,3... due to their specific data. Therefore, simply open the SQL view of your query and add your needed 15 values.
TRANSFORM ...
SELECT ...
FROM ...
GROUP BY ...
PIVOT ...
IN ('A455AJ', 'A456AK', ...)
Do note, this IN() clause serves as both a filter and order of columns. Items can be quoted or not.
Alternatively, if you need to subset columns by form controls, use IN inside a WHERE clause condition of a derived table subquery -allowable in crosstab queries.
PARAMETERS [Forms]![frm_BulkComparison_Select]![Item 1] TEXT(255),
[Forms]![frm_BulkComparison_Select]![Item 2] TEXT(255),
...;
TRANSFORM ...
SELECT ...
FROM
(SELECT * FROM myTable
WHERE col_item IN ([Forms]![frm_BulkComparison_Select]![Item 1],
[Forms]![frm_BulkComparison_Select]![Item 2],
...)
) As sub
GROUP BY ...
PIVOT ...
How do you do a crosstab query in Access with a fixed amount of columns
Subquery? That's one of my weaknesses when it comes to Access so I can't help you there. I'd suggest posting the SQL of the query though so others can take a look. Also what happens when you run the query?
The following is a query that I'm using to give me costs for the last ten years for a given unit.
TRANSFORM Sum(ServiceRecords.srTotalCost) AS AnnualCost
SELECT ServiceRecords.srEquipmentID
FROM ServiceRecords
GROUP BY ServiceRecords.srEquipmentID
PIVOT "C" & DateDiff("yyyy",[srServiceDate],Date()) In ("C9","C8","C7","C6","C5","C4","C3","C2","C1","C0");
The trick is after the PIVOT. As I want the last ten years worth of data the "C" & DateDiff portion sets up a string variable call C0 to C9. The portion after the In tells which column to stuff things into.
Another query which pulls in data about the Equipment calls this query. The term we generally use for such is stacked queries.
If this isn't enough to get you going please indicate what type of data you are trying to create a cross tab.
Fellow Access MVP, Allen Browne has a good page on this topic. Crosstab query techniques
How to create an Access crosstab query with totals for the columns AND the rows?
You'd basically have to run your query twice - once to get the data and then a second time to provide the aggregates. If you're set on doing this, make the first query to return data its own object. Then make another query to aggregate the first one another object. Create a final third query object to combine the two using a UNION as you mentioned.
Although I have to say I don't really recommend this. It sounds like you're trying to force the SQL to generate something that's really presentational information (i.e. it doesn't belong in the same dataset).
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;
PostgreSQL Crosstab Query
Install the additional module tablefunc once per database, which provides the function crosstab(). Since Postgres 9.1 you can use CREATE EXTENSION for that:
CREATE EXTENSION IF NOT EXISTS tablefunc;
Improved test case
CREATE TABLE tbl (
section text
, status text
, ct integer -- "count" is a reserved word in standard SQL
);
INSERT INTO tbl VALUES
('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
, ('C', 'Inactive', 7); -- ('C', 'Active') is missing
Simple form - not fit for missing attributes
crosstab(text) with 1 input parameter:
SELECT *
FROM crosstab(
'SELECT section, status, ct
FROM tbl
ORDER BY 1,2' -- needs to be "ORDER BY 1,2" here
) AS ct ("Section" text, "Active" int, "Inactive" int);
Returns:
Section | Active | Inactive
---------+--------+----------
A | 1 | 2
B | 4 | 5
C | 7 | -- !!
- No need for casting and renaming.
- Note the incorrect result for
C: the value7is filled in for the first column. Sometimes, this behavior is desirable, but not for this use case. - The simple form is also limited to exactly three columns in the provided input query: row_name, category, value. There is no room for extra columns like in the 2-parameter alternative below.
Safe form
crosstab(text, text) with 2 input parameters:
SELECT *
FROM crosstab(
'SELECT section, status, ct
FROM tbl
ORDER BY 1,2' -- could also just be "ORDER BY 1" here
, $$VALUES ('Active'::text), ('Inactive')$$
) AS ct ("Section" text, "Active" int, "Inactive" int);Returns:
Section | Active | Inactive
---------+--------+----------
A | 1 | 2
B | 4 | 5
C | | 7 -- !!
Note the correct result for
C.The second parameter can be any query that returns one row per attribute matching the order of the column definition at the end. Often you will want to query distinct attributes from the underlying table like this:
'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
That's in the manual.
Since you have to spell out all columns in a column definition list anyway (except for pre-defined crosstabN() variants), it is typically more efficient to provide a short list in a VALUES expression like demonstrated:
$$VALUES ('Active'::text), ('Inactive')$$)
Or (not in the manual):
$$SELECT unnest('{Active,Inactive}'::text[])$$ -- short syntax for long lists
I used dollar quoting to make quoting easier.
You can even output columns with different data types with
crosstab(text, text)- as long as the text representation of the value column is valid input for the target type. This way you might have attributes of different kind and outputtext,date,numericetc. for respective attributes. There is a code example at the end of the chaptercrosstab(text, text)in the manual.
db<>fiddle here
Effect of excess input rows
Excess input rows are handled differently - duplicate rows for the same ("row_name", "category") combination - (section, status) in the above example.
The 1-parameter form fills in available value columns from left to right. Excess values are discarded.
Earlier input rows win.
The 2-parameter form assigns each input value to its dedicated column, overwriting any previous assignment.
Later input rows win.
Typically, you don't have duplicates to begin with. But if you do, carefully adjust the sort order to your requirements - and document what's happening.
Or get fast arbitrary results if you don't care. Just be aware of the effect.
Advanced examples
Pivot on Multiple Columns using Tablefunc - also demonstrating mentioned "extra columns"
Dynamic alternative to pivot with CASE and GROUP BY
\crosstabview in psql
Postgres 9.6 added this meta-command to its default interactive terminal psql. You can run the query you would use as first crosstab() parameter and feed it to \crosstabview (immediately or in the next step). Like:
db=> SELECT section, status, ct FROM tbl \crosstabview
Similar result as above, but it's a representation feature on the client side exclusively. Input rows are treated slightly differently, hence ORDER BY is not required. Details for \crosstabview in the manual. There are more code examples at the bottom of that page.
Related answer on dba.SE by Daniel Vérité (the author of the psql feature):
- How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?
How can I sort the columns in a crosstab query, when the column data is dynamic?
Having encountered the same scenario various times, I prepared a repeatable way to add an In list to the end of the PIVOT clause. Doing so will sort the columns in the crosstab query by order of the elements in the pivotfield In list. Documentation for this construct is available from MSDN. The solution is a procedure that needs triggered by a command button on a form or another event. Please see the screen shots below the Sub.
Public Sub SortPivotColumns(querynameSource As String, queryname As String, SortName As String, SortColumnNameField As String, SortIndexName As String, NonPivotFieldCount As Integer, ParamArray ParamArr() As Variant)
' This sub goes through several steps to effectively adds an In list that sorts the 'Columns' of a crosstab query in MS Access
' 13 November 2012
' E Easterly
'
' This technique uses several components.
' 1) The original unmodified cross tab query (querynameSource)
' 2) The resulting, columns-have-been-sorted query (query)
' 3) An index table which has two columns, a numeric index used for sorting and the column name
' 4) A table or query that can be joined on the column names of the cross tab query to update the index table
' The name of the table or query would be 'SortName'
' The field in 'SortName' that the crosstab query columns are joined against is the 'SortColumnNameField'
' The field in 'SortName' that has the desired order is the SortIndexName
' 5) A number which specifies the count of non-pivot/row heading columns (NonPivotFieldCount)
' 6) An optional array that contains any parameters needed for the query
'
'
' USE:
'
' SortPivotColumns "qryCrosstab_Initial", _
' "qryCrosstab_Sorted", _
' "tblKeyDescriptions", _
' "Descriptions", _
' "NumericIndexForSorting", _
' 1
'
'
'
'
Dim rs As DAO.Recordset
Dim db As Database
Dim fld As DAO.Field
Dim sql As String
Dim ColumnHeading As Variant
Dim qdf As QueryDef
Dim qdfSRC As QueryDef
Dim UpdateIndexSQL As Variant
DoCmd.SetWarnings False 'Turn off warnings
Set db = CurrentDb
Set qdfSRC = db.QueryDefs(querynameSource)
Set qdf = db.QueryDefs(queryname)
qdf.sql = qdfSRC.sql
If Not (IsEmpty(ParamArr)) Then
Dim i As Integer
For i = 0 To UBound(ParamArr)
qdf.Parameters(i) = ParamArr(i)
Next
End If
' First, get the list of fields from the query
Set rs = qdf.OpenRecordset
' Then, create a temporary indexing table
If Not IsNull(DLookup("Name", "MSysObjects", "Name='ttblSortCrosstabColumns' And Type In (1,4,6)")) Then
db.Execute "DROP TABLE ttblSortCrosstabColumns"
End If
db.Execute "CREATE TABLE ttblSortCrosstabColumns (FieldIndex INTEGER , ColumnName TEXT(250))"
' And populate it with the current index and column names from queryname
For Each fld In rs.Fields
If fld.OrdinalPosition > (NonPivotFieldCount - 1) Then
DoCmd.RunSQL "Insert into ttblSortCrosstabColumns VALUES(" & fld.OrdinalPosition & ", """ & fld.Name & """)"
End If
Next fld
Set fld = Nothing
rs.Close
Set rs = Nothing
' Now, the temporary table is joined with the sort table/query and the indexes are updated
UpdateIndexSQL = (" UPDATE ttblSortCrosstabColumns " & _
" INNER JOIN " & SortName & " ON ttblSortCrosstabColumns.ColumnName=" & SortName & "." & SortColumnNameField & _
" Set ttblSortCrosstabColumns.FieldIndex = [" & SortIndexName & "]")
DoCmd.RunSQL (UpdateIndexSQL)
' Then, the column headings are added to a string to prepare the In list
sql = "SELECT ttblSortCrosstabColumns.ColumnName FROM ttblSortCrosstabColumns ORDER BY ttblSortCrosstabColumns.FieldIndex"
Set rs = db.OpenRecordset(sql)
rs.MoveFirst
ColumnHeading = "'" & rs.Fields(0).Value & "'"
rs.MoveNext
Do While Not rs.EOF
ColumnHeading = ColumnHeading & ", '" & rs.Fields(0).Value & "'"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
' db.Execute "DROP TABLE ttblSortCrosstabColumns"
Dim cs As Variant
' Set qdf = db.QueryDefs(queryname) ' may not need this
' The query is updated with the In list
cs = Left$(qdf.sql, Len(qdf.sql) - 3) & " In(" & ColumnHeading & ");"
qdf.sql = cs
' Take a look at the resulting query sql by uncommenting the below section
' Debug.Print cs
DoCmd.SetWarnings True 'Turn warnings back on
End Sub
In the below screen shot, note the tblKeyDescriptions and the tblPFValues. These are from the question. qryCrosstab_Initial is analogous to the query provided in the above question. The form is used to run the procedure and open the before and after queries.
An integer field (NumericIndexForSorting) was added to tblKeyDescriptions because the sub requires a numeric index for sorting the column names.
Now, inspect the In list highlighted in the SQL view of the initial and sorted queries.
This is all that is needed to order the columns in a crosstab query. Dynamically generating the In list is the purpose of the sub.
Note: the sub needs to be run each time the query is run, so using an event such as a command button On Click event to tie the sequence together is helpful.
Related Topics
SQL Server - Lack of Natural Join/X Join Y Using(Field)
Get SQL Xml Attribute Value Using Variable
Conditional Lead/Lag Function Postgresql
Does Assigning Stored Procedure Input Parameters to Local Variables Help Optimize the Query
Rowset Does Not Support Scrolling Backward
Concurrency Handling of SQL Transactrion
Faster Way to Insert, via Script, in Oracle
Using Ssis to Extract a Xml Representation of Table Data to a File
How to Expand Comma Separated Values into Separate Rows Using SQL Server 2005
Left Join Query Not Returning All Rows in First Table
Check Bound Datatable for Null Value Vb.Net
How to Delete from a Table Where Id Is in a List of Ids
SQL Row_Number() Function in Where Clause