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
Pivot - SQL - values from SubQuery
No. This can only be done using a dynamic query. I would be really interested to find out as well if there is a way.
There are some examples which a quick Google search found using COALESCE to create the column list. However I prefer to create the list of columns using STUFF. However I did find this article about the use CTE's and dynamic pivots which may be of assitance as well
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
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
SQL Pivot with subquery
CREATE TABLE #Test ([value] char(1), effective_date date)
INSERT INTO #Test ([value], effective_date) VALUES
('A', '2000-10-31'),
('A', '2000-11-30'),
('B', '2000-10-31'),
('B', '2000-11-30'),
('C', '2001-10-31'),
('C', '2001-12-31')
SELECT *
FROM
(
SELECT [value] AS 'Display', [value], effective_date FROM #Test
) AS SRC
PIVOT
(
COUNT([value])
FOR effective_date
IN ([2000-10-31], [2000-11-30], [2001-10-31], [2001-12-31])
) AS PVT
DROP TABLE #Test
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), () )
Subquery for PIvot table in sql
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover_id])
FROM #Registered2 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + ' from
(
select*
from Registered2
) x
pivot
(
SUM(Registered_customer_Count)
for [Offer_cover_id] in (' + @cols + ')
) p '
execute(@query)
Related Topics
How to Remove Part of the String in Oracle
Distinct Listagg That Is Inside a Subquery in the Select List
Update a Single Row with T-Sql
Insert into Values with Where Clause
How to Use a Returned Column Value as a Table Name in an SQLite Query
Microsoft Access Query Should Return True or True and False, Only Returns True
Inserting Multiple Rows with One Insert Command
Access: Create Table If It Does Not Exist
Bigquery Update Nested Array Field
How to Find All Open/Active Connections in Db2 (8.X)
On Delete Cascade for Self-Referencing Table
Postgres Query of an Array Using Like
Finding Continuous Ranges in a Set of Numbers
Like Operation Returns No Rows on Nvarchar Column Filter If the Column Data Start with Numeric
Dynamic Pivot Needed with Row_Number()