MS SQL Server pivot table with subquery in column clause
for dynamic number of columns you have to use dynamic SQL
declare
@cols nvarchar(max),
@stmt nvarchar(max)
select @cols = isnull(@cols + ', ', '') + '[' + T.POINTNAME + ']' from (select distinct POINTNAME from TABLE1) as T
select @stmt = '
select *
from TABLE1 as T
pivot
(
max(T.VALUE)
for T.POINTNAME in (' + @cols + ')
) as P'
exec sp_executesql @stmt = @stmt
SQL FIDDLE EXAMPLE
SQL Server sub query with pivot
Since you're building the query dynamically you want to use @query as a string and inject the @cols into it.
I would make some minor changes to the query too (to get the sort of variants correct) and the query below should give you the desired output:
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + ChrLocus + ']', '[' + ChrLocus + ']')
FROM
(
SELECT DISTINCT Chromosome+'_'+ CAST(Locus AS VARCHAR(10))ChrLocus
FROM genotypeQA
) PV
ORDER BY ChrLocus
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT Strain_ID, COLNAMES as Variants, ' + @cols + '
FROM
( -- Source data for pivoting
SELECT CONCAT(Chromosome,''_'',Locus) ChrLocus,Strain_ID,
Variants, COLNAMES, sort
FROM genotypeQA
CROSS APPLY(VALUES (1, Variant_A,''Variant_A''),(2, Variant_B,''Variant_B''),(3, Variant,''Variant''))
AS COLUMNNAMES(Sort, Variants,COLNAMES)
) x
PIVOT
(
--Defines the values in each dynamic columns
min (Variants)
-- Get the names from the @cols variable to show as column
FOR ChrLocus IN ('+ @cols +')
) p
order by strain_id, sort
;'
--print @query
EXEC SP_EXECUTESQL @query
The output would be:
Strain_ID Variants Gm09_40907915 Gm09_422384 Gm09_422720 Gm09_424439 Gm09_425375 Gm09_425581 Gm09_43921862
---------- --------- ------------- ----------- ----------- ----------- ----------- ----------- -------------
DS11.46096 Variant_A G G A C G T C
DS11.46096 Variant_B A A G A T C A
DS11.46096 Variant GA GA AG CA GT TC CA
Pivot Table with and with out SubQuery
You only have the columns Name and Occupation - if you pivot by Occupation and aggregate the name only one row will be returned. By adding the row_number the aggregation will not reduce the output to a single row since each row has a differnet row_number.
SQL Pivot Table - Subqueries
Here's a simple way to do what you're looking for:
First, create your table of month values. I made a simple temp table with a single column.
CREATE TABLE #Dates (MonthNum INT)
INSERT INTO #Dates
(
MonthNum
)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
Next, you can put your existing query into a CTE, then LEFT JOIN to your table of months. You'll want to put your columns into a SUM'd CASE statement, like so:
;WITH Aggregation AS
(
SELECT
TotalMins = SUM(Minutes)
,DateMonth = MONTH(Date)
,ID1
,PC1
FROM #User_Time_Log
WHERE
(UserID = 1)
AND (YEAR(Date) = 2018)
GROUP BY
MONTH(Date)
,ID1
,PC1
)
SELECT
d.MonthNum
,NonID1 = SUM(CASE WHEN ID1 = 0 THEN TotalMins ELSE 0 END)
,TimeID1 = SUM(CASE WHEN ID1 = 1 THEN TotalMins ELSE 0 END)
,TimePC1 = SUM(CASE WHEN ID1 = 0 THEN ROUND((PC1/100)*TotalMins,0) ELSE 0 END)
,TimePC1ID1 = SUM(CASE WHEN ID1 = 1 THEN ROUND((PC1/100)*TotalMins,0) ELSE 0 END)
FROM #Dates d
LEFT JOIN Aggregation a ON d.MonthNum = a.DateMonth
GROUP BY d.MonthNum
Output would then look like this:
MonthNum NonID1 TimeID1 TimePC1 TimePC1ID1
1 498 0 306 0
2 478 17 365 3
3 328 3 253 0
4 533 33 233 23
5 572 68 134 49
6 0 0 0 0
7 0 0 0 0
8 0 0 0 0
9 0 0 0 0
10 32 167 0 167
11 0 0 0 0
12 0 0 0 0
EDIT:
The ROUND() function call can be changed slightly to accomodate your need for decimal results. The first parameter of ROUND() is the expression you want to round, and the second is the number of decimal places to round to. Positive numbers indicate the number of places to the right of the decimal to round to. Negative numbers indicate the number of places to the left of the decimal to round to. So if you set it to 2, you'll get an answer rounded to the nearest hundredth.
But there's one more tweak we need. PC1 and TotalMins are both assumed to be INTs in my answer. So we have to give the SQL engine a little help so that it calculates the answer as a DECIMAL. By CAST()ing the INTs to DECIMALs, SQL will perform the arithmetic op as decimal math instead of integer math. You'd just have to change TimePC1 and TimePC1ID1 like so:
,TimePC1 = SUM(CASE WHEN ID1 = 0 THEN ROUND((CAST(PC1 AS DECIMAL)/100)*CAST(TotalMins AS DECIMAL),2) ELSE 0 END)
,TimePC1ID1 = SUM(CASE WHEN ID1 = 1 THEN ROUND((CAST(PC1 AS DECIMAL)/100)*CAST(TotalMins AS DECIMAL),2) ELSE 0 END)
Then the output looks like this:
MonthNum NonID1 TimeID1 TimePC1 TimePC1ID1
1 498 0 306.000000 0.000000
2 478 17 365.000000 3.000000
3 328 3 253.000000 0.000000
4 533 33 233.000000 23.000000
5 572 68 134.000000 49.000000
6 0 0 0.000000 0.000000
7 0 0 0.000000 0.000000
8 0 0 0.000000 0.000000
9 0 0 0.000000 0.000000
10 32 167 12.600000 167.000000
11 0 0 0.000000 0.000000
12 0 0 0.000000 0.000000
How to use variables as pivot columns on SQL Server
This is a known limitation of the PIVOT functionality. You must have a pre-defined set of columns within your IN list. They can not change dynamically. So in order to do this you would need to utilize dynamic SQL to change your result set based on your input values.
There are many guides on how to do this out there, but I will give an example of how you could apply it to your script (with some minor reformatting added). I did correct two issues in order to run it. 1.Add alias to your subquery subquery_alias 2. Add GROUP BY clause to your aggregate query
DECLARE @TODAY DATE = CAST(GETDATE() AS DATE);
DECLARE @CUR_YR INT = YEAR(@TODAY);
DECLARE @PREV_YR INT = @CUR_YR-1;
DECLARE @Sql nvarchar(max) =
N'SELECT
*
FROM
(SELECT
BUS
,DET
,[STR]
,COALESCE(' + QUOTENAME(CAST(@PREV_YR AS nvarchar(10))) + N',0) AS PREV_YR
,COALESCE(' + QUOTENAME(CAST(@CUR_YR AS nvarchar(10))) + N',0) AS CUR_YR
FROM
(SELECT
YR
,BUS
,DET
,[STR]
,COUNT(ORD_ID_SE) AS ORDS
FROM
(<SUBQUERY>) AS subquery_alias
GROUP BY
YR
,BUS
,DET
,[STR]) AS T
PIVOT(SUM(ORDS) FOR YR IN (' + QUOTENAME(CAST(@PREV_YR AS nvarchar(10))) + N',' + QUOTENAME(CAST(@CUR_YR AS nvarchar(10))) + N')) AS pivot_table) AS F';
EXEC sys.sp_executesql @Sql;
That being said, in this case you are re-aliasing the output of your pivot tables to be PREV_YR and CUR_YR respectively. So you really don't need dynamic column names and if you are only ever going to use two different variables in this manner then it makes more sense to pivot it with a SUM(CASE WHEN....END) method. Like so:
DECLARE @TODAY DATE = CAST(GETDATE() AS DATE);
DECLARE @CUR_YR INT = YEAR(@TODAY);
DECLARE @PREV_YR INT = @CUR_YR-1;
SELECT
BUS
,DET
,[STR]
,SUM(CASE WHEN YR = @PREV_YR THEN 1 ELSE 0 END) AS PREV_YR
,SUM(CASE WHEN YR = @CUR_YR THEN 1 ELSE 0 END) AS CUR_YR
FROM
(<SUBQUERY>) AS subquery_alias
GROUP BY
BUS
,DET
,[STR];
Doing this you can completely avoid dynamic SQL and a more complicated pivot query.
Oracle SQL - Pivot table rows to column and use sub query in pivot
Just use conditional aggregation:
SELECT COALESCE(customer, 'Grand Total') as customer,
SUM(CASE WHEN Hotel = 'Royal Palms' THEN 1 ELSE 0 END) as "Royal Palms",
SUM(CASE WHEN Hotel = 'Beverly Hills' THEN 1 ELSE 0 END) as "Beverly Hills",
SUM(CASE WHEN Hotel = 'Ritz-Carlton' THEN 1 ELSE 0 END) as "Ritz-Carlton" ,
COUNT(*) as "Grand Total",
COUNT(Booked_Status) as "Num Booked"
FROM CUST_HOTEL_VIEW
GROUP BY ROLLUP(CUSTOMER)
ORDER BY CUSTOMER;
Conditional aggregation is much more flexible then pivot. Personally, I see no reason for the pivot syntax: it does one thing well, but is not a building block the way tradition SQL statements are.
ROLLUP() is also quite helpful. You can also use:
GROUP BY GROUPING SETS ( (CUSTOMER), () )
Convert Rows to columns using 'Pivot' in SQL Server
If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.
It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.
First up, here are some quick table definitions and data for use:
CREATE TABLE yt
(
[Store] int,
[Week] int,
[xCount] int
);
INSERT INTO yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);
If your values are known, then you will hard-code the query:
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
See SQL Demo
Then if you need to generate the week number dynamically, your code will be:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Week)
from yt
group by Week
order by Week
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT store,' + @cols + ' from
(
select store, week, xCount
from yt
) x
pivot
(
sum(xCount)
for week in (' + @cols + ')
) p '
execute(@query);
See SQL Demo.
The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
SQL Pivot Table On A Common Table Expression
It's extremely common to see select clause do something like this if data has NULLs:
SELECT ISNULL([FieldName],0) as [FieldName] FROM SOMTEABLE
Well it's no different for a query using PIVOT, you still solve the issue of NULLs through the SELECT clause although one problem here is that you have used "select *" and this shortcut hides the positions where you can solve the problem.
Syntax using a CTE. Note what was "select *" is now detailed, and ISNULL(.....,0) used.
;WITH
cte
AS (
SELECT
pl.ProductionLine AS ProductionLine
, pd.ProductionDate AS ProductionDate
, pd.UnitsProduced AS UnitsProduced
FROM ProductionLine pl
JOIN ProductionData pd
ON pl.ID = pd.ProductionLine_ID
)
SELECT
ISNULL([Line A], 0) [Line A]
, ISNULL([Line B], 0) [Line B]
FROM (
SELECT
ProductionLine
, ProductionDate
, UnitsProduced
FROM cte
) x
PIVOT
(
MAX(UnitsProduced)
FOR ProductionLine IN ([Line A], [Line B])
) pvt
;
Personally I don't recommend using CTE's unless there is a demonstrated need for it, which isn't true here and a simple subquery will do exactly the same job with slightly simpler syntax.
SELECT
ISNULL([Line A], 0) [Line A]
, ISNULL([Line B], 0) [Line B]
FROM (
SELECT
pl.ProductionLine AS ProductionLine
, pd.ProductionDate AS ProductionDate
, pd.UnitsProduced AS UnitsProduced
FROM ProductionLine pl
JOIN ProductionData pd
ON pl.ID = pd.ProductionLine_ID
) x
PIVOT
(
MAX(UnitsProduced)
FOR ProductionLine IN ([Line A], [Line B])
) pvt
;
See this working at SQLFiddle
COALESCE() or ISNULL()
I have used ISNULL() in the above which is TSQL specific, and so is the PIVOT syntax. Stackoverflow recommend using standard SQL if possible so please note it would be possible to use COALESCE() instead of ISNULL() in the queries above. But also note those functions are not exactly the same.
How to make two pivot column with same column name using SQL Server?
I think this does roughly what you want:
declare @t table (ItemLookupCode varchar(20), StoreID int, DepartmentID int, Weeks int,
QtySold decimal(10,4), AsOfWeekOnHand decimal(10,4))
insert into @t(ItemLookupCode,StoreID,DepartmentID,Weeks,QtySold,AsOfWeekOnHand) values
('610759C2000',1001,23,30, 0 ,1.5 ),
('610759C2000',1001,23,31, 0 , 0 ),
('610759C2000',1004,23,30, 0 , 2 ),
('610759C2000',1004,23,31, 0 ,3.5 ),
('610759C2000',1201,23,30,0.6395, 1 ),
('610759C2000',1201,23,31,0.6395, 2 )
select
*
from
(select ItemLookupCode,StoreID,DepartmentID,
CONVERT(varchar(13),Weeks) + 'Qty' as Weeks,
QtySold from @t) t1
pivot (SUM(QtySold) for Weeks in ([30Qty],[31Qty])) p1
cross apply
(select CONVERT(varchar(13),Weeks) + 'AsOf' as Weeks,AsOfWeekOnHand
from @t t2
where t2.ItemLookupCode = p1.ItemLookupCode and
t2.DepartmentID = p1.DepartmentID and
t2.StoreID = p1.StoreID) t2
pivot (SUM(AsOfWeekOnHand) for Weeks in ([30AsOf],[31AsOf])) p2
Results:
ItemLookupCode StoreID DepartmentID 30Qty 31Qty 30AsOf 31AsOf
-------------------- ----------- ------------ ---------- -------- -------- -------
610759C2000 1001 23 0.0000 0.0000 1.5000 0.0000
610759C2000 1004 23 0.0000 0.0000 2.0000 3.5000
610759C2000 1201 23 0.6395 0.6395 1.0000 2.0000
Of note:
You cannot pivot twice using the same column(s) - after the pivot, the columns mentioned in the first part of the pivot clause no longer exist, having been replaced by the bracketed new column names.
We have to do an
applyrather than aJOINto a subquery to avoid introducing duplicate columns (e.g.ItemLookupCodewould appear twice in the result set ift2was ajointo a subquery)I took the opportunity to rename the
Weekscolumns in the subquerieswhen we use the
APPLYwe have to usep1as the outer reference - aPIVOTproduces a completely new result set that replaces any existing result set/aliases.As mentioned in my answer to your earlier question, a
PIVOTeffectivelyGROUP BYs all columns not mentioned in thePIVOTclause - so why aren't the columns produced by the firstPIVOTa concern during the second pivot? Because we already know that each combination ofItemLookupCode,StoreIDandDepartmentID, by themselves are unique, due to the firstPIVOT.
Related Topics
Finding the Count of Characters and Numbers in a String
SQL Server Case .. When .. in Statement
SQL Server 2008 Insert with While Loop
Postgresql Function Definition in Squirrel: Unterminated Dollar-Quoted String
Left Outer Join and an Additional Where Clause
Split a Single Column of Data with Comma Delimiters into Multiple Columns in Ssis
SQL Conversion from Varchar to Uniqueidentifier Fails in View
Check If Entry in Table a Exists in Table B
Changing SQL Server Database Sorting
Postgres Trigger After Insert Accessing New
Read the Log File (*.Ldf) in SQL Server 2008
How to Alter a Table for Identity Specification Is Identity SQL Server
Workaround for Ora-00997: Illegal Use of Long Datatype
Default Getdate for Insert Date