Getting a Dynamically-Generated Pivot-Table into a Temp Table
you could do this:
-- add 'loopback' linkedserver
if exists (select * from master..sysservers where srvname = 'loopback')
exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
go
declare @myDynamicSQL varchar(max)
select @myDynamicSQL = 'exec sp_who'
exec('
select * into #t from openquery(loopback, ''' + @myDynamicSQL + ''');
select * from #t
')
EDIT: addded dynamic sql to accept params to openquery
Inserting dynamic pivot Result into Temp table
Try this Query...
First Drop the #Final if exists....
then create the #Final table...
then Perfrom your old Query.....will not give error Invalid object name '#Final'. this type of error
CREATE TABLE #TBL (Dates DATE, [Year] INT, Amt MONEY,Item Varchar(100))
INSERT INTO #TBL VALUES
('2018-06-01',2018,34,'Milk'),('2018-07-01',2018,99,'Rice'),('2018-08-01',2018,77,'Rice'),('2018-09-01',2018,26,'Rice'),
('2018-10-01',2018,75,'Orange'),('2018-11-01',2018,94,'Grapes'),('2018-12-01',2018,80,'Grapes'),('2019-01-01',2019,9,'Grapes'),
('2019-02-01',2019,52,'Milk'),('2019-03-01',2019,28,'Orange'),('2019-04-01',2019,61,'Orange'),('2019-05-01',2019,51,'Milk');
IF OBJECT_ID('tempdb..#Final', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE #Final
CREATE TABLE #Final ([Year] INT, Amt MONEY,Item Varchar(100))
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME( Item)
FROM #TBL
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query =
'SELECT
*
INTO #Final
FROM (
SELECT [Year],Amt ,Item
FROM #TBL ) FG
PIVOT
(
SUM(Amt) FOR Item IN ('+@cols+')
) pv'
EXEC(@query)
select * from #Final
DROP TABLE #TBL
Storing dynamic Pivot result into a temporary table in SQL Server
If you are using apply
then why you need further same logic in PIVOT
(i.e. Channel + CONVERT(Varchar(4), Year)
) which is already available in apply
.
So, i would use Value
instead in PIVOT
:
. . .
Pivot (sum([Payments]) For [Value] in ([HV2012],[HV2013],[HV2014],[NL2012]) ) p,
So, your updated Dynamic SQL
would be :
Declare @SQL varchar(max) = '
if object_id(''tempdb..##TempTable'') is not null
begin
drop table ##TempTable
end
create table ##TempTable([Id] int null, ' +
Stuff((Select Distinct ','+QuoteName(Channel + CONVERT(Varchar(4), Year)) + ' Varchar(20) null'
From [dbo].MyTable
Order By 1
For XML Path('')),1,1,'')+ ')
INSERT INTO ##TempTable
Select *
From (
Select A.ID, A.Payments
,B.*
From [dbo].MyTable a
Cross Apply ( values ( Channel + CONVERT(Varchar(4), Year)
)) B ([Value])
) S
Pivot (sum([Payments]) For [Value] in
(' + Stuff((Select Distinct ','+QuoteName(Channel + CONVERT(Varchar(4), Year))
From #tm
Order By 1
For XML Path('')),1,1,'') + ') ) p'
print @sql
Exec(@SQL)
SELECT * FROM ##TempTable
I have made no of changes as there are many correction needs to be done prior to execution.
INSERT INTO Temp Table Dynamic Pivot
what @MM93 told you, that is correct answer, but here is full code
IF OBJECT_ID('TEMPDB.dbo.##TempTableTesting') IS NOT NULL DROP TABLE ##TempTableTesting
declare @sql nvarchar(300) = 'SELECT MaterialScopeCode as Scope, ' + @ColumnName + '
into ##TempTableTesting
FROM #tt
PIVOT(SUM(CountofItemNo)
FOR Outage IN (' + @ColumnName + ')) AS PVTTable'
execute sp_executesql @sql
select * from ##TempTableTesting
So in your case you have to use ##TempTable as those tables (global) are available to ALL sessions, #TempTable won't work in your case
Dynamic Pivot using Temp tables
To get each Dx_Code of a Patient_Account in the same line, you need to GROUP BY Patient_Account
, and use some aggregate function (like MIN() or MAX()) to get the Dx_Code and Dx_Desctiption.
Another way to make a dynamic query:
SELECT STRING_AGG(query_piece, '')
FROM (
(SELECT 'SELECT Patient_Account,' AS query_piece)
UNION ALL
(SELECT
CONCAT('MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Code END) AS ', Dx_Rank, ', ',
'MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Desctiption END) AS ', Dx_Rank, '_Description, ')
FROM Diagnosis
GROUP BY Dx_Rank)
UNION ALL
(SELECT 'Visit_Key
FROM Diagnosis
GROUP BY Patient_Account, Visit_Key;')) AS dynamic_query;
The result of the dynamic query is the query you have to execute to get the desired result:
SELECT Patient_Account,MIN(CASE WHEN Dx_Rank = 'Dx_1' THEN Dx_Code END) AS Dx_1, MIN(CASE WHEN Dx_Rank = 'Dx_1' THEN Dx_Desctiptio END) AS Dx_1_Description, MIN(CASE WHEN Dx_Rank = 'Dx_2' THEN Dx_Code END) AS Dx_2, MIN(CASE WHEN Dx_Rank = 'Dx_2' THEN Dx_Desctiptio END) AS Dx_2_Description, MIN(CASE WHEN Dx_Rank = 'Dx_3' THEN Dx_Code END) AS Dx_3, MIN(CASE WHEN Dx_Rank = 'Dx_3' THEN Dx_Desctiptio END) AS Dx_3_Description, Visit_Key
FROM Diagnosis
GROUP BY Patient_Account, Visit_Key;
Output with the example data in your question:
Patient_Account | Dx_1 | Dx_1_Description | Dx_2 | Dx_2_Description | Dx_3 | Dx_3_Description | Visit_Key |
---|---|---|---|---|---|---|---|
123456789 | J20 | Left Hip is Broken | A32 | Left Knee is broken | R4786 | Left Ankle is broken | #PAS203234 |
987654321 | DF346 | Right Arm is broken | DT342.12 | Right Wrist is broken | #PAS435678 |
Dynamic Pivot Results to a Temp Table
You should be able to use INTO Clause. I added INTO into your example.
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(QT.QUESTION_DESC)
FROM #QUES_TEMP QT
GROUP BY QT.QUESTION_DESC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT EVAL_ID, AuditType, ' + @cols + ' from
(
select QT.EVAL_ID,
QT.AuditType,
QT.SCORE,
QT.QUESTION_DESC
into ##tmp
from #QUES_TEMP QT
) x
pivot
(
max(SCORE)
for QUESTION_DESC in (' + @cols + ')
) p '
execute(@query);
SELECT * FROM ##tmp
Get Dynamic Pivot Result into temp table SQL Server
You can't store inside a #
(temp table) without defining it in the parent scope when using sp_executesql
. In your case you have a dynamic pivot
and you don't know what and how many columns are going to be there.
sp_executesql
runs in a different session (sp_executesql creates its own session) and temp tables are session specific.
For your scenario you can use ##
(global temp table). You can change your query like following.
SELECT @query =
'
SELECT
* into ##temp
FROM
(SELECT
Count(dbo.InventoryBiltyMaster.ID) AS BiltyCount,
--dbo.InventoryBiltyMaster.InventoryProductMasterID,
--dbo.InventoryBiltyMaster.VehicleMasterID,
dbo.InventoryProductMaster.Name,
dbo.VehicleMaster.VehicleNumber
FROM
dbo.InventoryBiltyMaster
INNER JOIN dbo.InventoryPartyProductPriceMaster ON dbo.InventoryBiltyMaster.InventoryPartyProductPriceMasterID = dbo.InventoryPartyProductPriceMaster.ID
INNER JOIN dbo.InventoryProductMaster ON dbo.InventoryPartyProductPriceMaster.InventoryProductMasterID = dbo.InventoryProductMaster.ID
INNER JOIN dbo.VehicleMaster ON dbo.InventoryBiltyMaster.VehicleMasterID = dbo.VehicleMaster.ID
WHERE
dbo.InventoryBiltyMaster.PartyMasterID = ' + CAST(@partymasterid as nvarchar(50)) + '
GROUP BY
dbo.InventoryBiltyMaster.InventoryProductMasterID,
dbo.InventoryProductMaster.Name,
dbo.InventoryBiltyMaster.VehicleMasterID,
dbo.VehicleMaster.VehicleNumber
)
AS S
PIVOT
(
MAX(BiltyCount)
FOR [Name] IN (' + LEFT(@cols, LEN(@cols)-1) + ')
)AS pvt';
EXEC SP_EXECUTESQL @query
--now you can use ##temp
Note: use of global temp table can lead to unpredictable behavior if it gets updated from multiple sessions, you may think of giving a unique name for each session.
How to store the result of Dymanic pivot into a temp table without specifying column names for a table?
It seems like I could make use of global temporary table in this way to get my desired results.
set @query = 'SELECT '+@cols+' into ##temp1 from
(
select Columnname, Rownum
, value
from #temp
) x
pivot
(
max(Value)
for Columnname in (' + @cols + ')
) p '
execute(@query)
select * from ##temp1
Age Email Name Phone
20 Bob@gmail.com Ben 888888888
20 Rob@gmail.com Bob 999999999
25 NULL Rob NULL
Related Topics
How to Return Only Work Time from Reservations in Postgresql
Sql: Select Top 3 Records + Sum of Quantity
How to Use Array_Agg() for Varchar[]
How to Create a Temporary Table from a Type
How to Insert into a Table That Specifies a Default Value for Every Column
Oracle: Can You Assign an Alias to the from Clause
Find Top 10 Latest Record for Each Buyer_Id for Yesterday's Date
Convert a String to a Date in Access
Create SQL Server Job Automatically
Is an Overuse of Nullable Columns in a Database a "Code Smell"
The Alter Table Statement Conflicted
Duplicate Columns with Inner Join
How to Make This SQL More Efficient
See SQL from Entityframework with Collection-Like Queries
How to Create Alternative Row Background Colors in Ssrs for Values in a Group