April 9, 2013 at 7:32 am
Hi All,
I need to create a table which will be having teh structure of the Result of the PIVOTED Query.
I am sure we can create a view for this when we are using Pivot.
I am creating the table so that I can Update the Data of a Column from Multiple Columns which we have Data needed.
Sudhir Nune
April 9, 2013 at 8:04 am
you didn't provide any real details or example code;
without seeing the query featuring PIVOT, all i can offer is to use the SELECT...INTO TABLENAME FROM MyTable format.
Lowell
April 9, 2013 at 8:24 am
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@cols2 AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.PAYMENT_TYPE)
FROM TEST_DATA_SRC c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @Cols2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+PAYMENT_TYPE+'],0) AS ['+PAYMENT_TYPE+']' FROM TEST_DATA_SRC GROUP BY PAYMENT_TYPE FOR XML PATH('')),2,8000)
set @query = 'SELECT name, ' + @cols2 + ' from
(
select name
, payment
, payment_type
from TEST_DATA_SRC
) x
pivot
(
max(payment)
for payment_type in (' + @cols + ')
) p '
EXECUTE(@QUERY)
April 9, 2013 at 8:30 am
i guess something like this, where you insert into ##GlobalTable would give you the results as a table so you can update from it later.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@cols2 AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.PAYMENT_TYPE)
FROM TEST_DATA_SRC c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @Cols2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+PAYMENT_TYPE+'],0) AS ['+PAYMENT_TYPE+']' FROM TEST_DATA_SRC GROUP BY PAYMENT_TYPE FOR XML PATH('')),2,8000)
set @query = 'SELECT name, ' + @cols2 + ' INTO ##GlobalTable from
(
select name
, payment
, payment_type
from TEST_DATA_SRC
) x
pivot
(
max(payment)
for payment_type in (' + @cols + ')
) p '
EXECUTE(@QUERY)
SELECT * FROM INTO ##GlobalTable
Lowell
April 9, 2013 at 8:37 am
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'INTO'.
🙁
Can you help me with the Exact query.
April 9, 2013 at 8:46 am
i edited the post, so re-copy it and try again.
the line for the global temp table should have been here:
set @query = 'SELECT name, ' + @cols2 + ' INTO ##GlobalTable from
Lowell
April 9, 2013 at 9:16 am
Tanks Lowwel, it is perfectly working.
But my problem is that I may not be able to use the Global Temp table.
Can we Have the local temp / Genral table in this case ???
April 9, 2013 at 9:29 am
sudhirnune (4/9/2013)
Tanks Lowwel, it is perfectly working.But my problem is that I may not be able to use the Global Temp table.
Can we Have the local temp / Genral table in this case ???
why don't you think you can use a global temp table?
as far as using a real table yes(maybe), but i thought your whole problem was you didn't know the columns/structure because the query is dynamic? if the # of columns varies, i'd stick with a global temp.
a temp table, probably no, because the table would go out of scope as soon as the the EXEC(@query) completes;
if you KNOW the structure/#columns, you could define it before you do the query.
Lowell
April 9, 2013 at 1:30 pm
Need Small help on the Below Query
Dynamic SQL has Column names which are not part of table, how to handle this to complete the process with out Error.
Ex: SAL = COM + ALL_1 + ALL_2
But the table will not have always the ALL_2, in the above situation, the update In Dynamic Query should work with out issues.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply