March 20, 2012 at 12:15 am
Hi everyone,
I am new to sql server and I do not know why the following is throwing errors. Any help is very, very much appreciated. Thanks in advance.
I want the rows of a table to be converted to columns (dynamic pivot). Now this table is formed by inner join on two tables.
DECLARE
@Users VARCHAR(2000) = 'SELECT field1, field2, Service, Value
INTO ##myTable2
FROM table1 INNER JOIN table2 ON table1PK = table2FK_table1PK
ORDER BY field1, field2
EXECUTE
(@Users)
SELECT
* FROM ##myTable2 // works fine
Result set from above select statement:
Field1 Field2 Service Value
Adam Cadman Service1 10
Adam Cadman Service2 30
Dana Jones Service1 40
Dana Jones Service2 50
Desired result set after dynamic pivot operation:
Field1 Field2 Service1 Service 2
Adam Cadman 10 30
Dana Jones 40 50
The follwing is my code:
DECLARE
@collect_list_service VARCHAR(5000)
DECLARE
@query VARCHAR(5000)
SELECT
@collect_list_service = @collect_list_service + STUFF(( SELECT '],[' + Service FROM ##myTable2
ORDER BY '],[' + Service
FOR XML PATH('')), 1, 2, '') + ']'
SET
@query =
'SELECT * INTO ##myTable6 FROM
(
SELECT field1, field2, service, value
FROM ##myTable2
)t
PIVOT (MIN(value) FOR service
IN ('
+@collect_list_service+')) AS pvt)) AS ResultTable'
(Here I do not intend to do MIN or SUM or anything, I just want the value to be displayed whatever it is...)
The above on executing shows commands executed successfully...
But when I do the following,
EXECUTE
(@query)
the following error is thrown
Must declare the scalar variable "@query".
I wonder what is wrong with this. I'm sure it has something to do with the scope of table variables/aliases. I have searched a lot but in vain.
PLEASE HELP!!!
March 20, 2012 at 1:39 am
Works fine for me though, after some minor modifications (modifications details in comments)
IF OBJECT_ID('TempDB..##myTable2') IS NOT NULL
DROP TABLE ##myTable2;
GO
CREATE TABLE ##myTable2
(
iD INT IDENTITY(1,1)
, Field1 VARCHAR(10)
, Field2 VARCHAR(10)
, [Service] VARCHAR(10)
, Value INT
);
INSERT INTO ##myTable2 (Field1 , Field2 ,Service ,Value)
SELECT 'Adam', 'Cadman', 'Service1' ,10
UNION ALL SELECT 'Adam', 'Cadman', 'Service2' ,30
UNION ALL SELECT 'Dana' ,'Jones' ,'Service1' ,40
UNION ALL SELECT 'Dana' ,'Jones' ,'Service2' ,50 ;
DECLARE @collect_list_service VARCHAR(5000) = '' -- Initialized with ''
DECLARE @query VARCHAR(5000) = '' -- Initialized with ''
SELECT -- Added DISTINCT due to the sample data; you may/may not need to add this
@collect_list_service = @collect_list_service + STUFF(( SELECT DISTINCT '],[' + Service FROM ##myTable2
ORDER BY '],[' + Service
FOR XML PATH('')), 1, 2, '') + ']'
SET
@query =
'SELECT * FROM
(
SELECT field1, field2, service, value
FROM ##myTable2
)t
PIVOT (MIN(value) FOR service
IN ('
+@collect_list_service+')) AS pvt' -- Removed AS ResultTable
EXEC (@query)
March 20, 2012 at 2:45 pm
Hello,
Thanks a lot for your reply. I made the changes according your post. But still this thing doesn't seem to work.
The following is my code, the errors, and a few questions if you could please answer them for me.
IF OBJECT_ID('TempDB..##myTable2') IS NOT NULL
DROP TABLE ##myTable2;
GO
declare @Users varchar(2000) = 'Select field1, field2, service, value into ##myTable2
from table1
Inner join table2 on table1PK = table2FK_table1PK
order by field1, field2'
EXECUTE(@Users)
--select * from ##myTable2
DECLARE @collect_list_service VARCHAR(5000) = ' '
DECLARE @query VARCHAR(5000) = ' '
SELECT @collect_list_service = @collect_list_service + STUFF(( SELECT DISTINCT '],[' + service FROM ##myTable2
ORDER BY '],[' + service
FOR XML PATH('')), 1, 2, '') + ']'
SET
@query =
'SELECT * FROM
(
SELECT field1, field2, service, value
FROM ##myTable2
)t
PIVOT (MIN(value) FOR service
IN ('+@collect_list_service+')) AS pvt))'
EXECUTE (@query)
--------------------------------
Errors:
(5028530 row(s) affected)
Msg 1038, Level 15, State 4, Line 7
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 105, Level 15, State 1, Line 7
Unclosed quotation mark after the character string 'Customer Service/B'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'Customer Service/B'.
P.S. The database has NULL values also for some service names. Is that the reason?
The value field is a bit (1/0) field.
Also, I have to execute all of the above in one go right? And what is the final table from which I will get the final result set? Please let me know. Thanks a lot...
March 20, 2012 at 2:52 pm
And when I hover over 'service' and '##myTable2' in the following, (it appears in red color) and shows 'invalid column name and object name' respectively.
SELECT @collect_list_service = @collect_list_service + STUFF(( SELECT DISTINCT '],[' + service FROM ##myTable2
ORDER BY '],[' + service
FOR XML PATH('')), 1, 2, '') + ']'
March 20, 2012 at 3:25 pm
i cant see the contents of ##mytable2 as you are joining table1 and table2 which i dont have.. i need to see your data (mock-up data, of course) to work on why ur query is failing..
March 23, 2012 at 4:48 pm
Hi @ColdCoffee,
Since the above was not working, I tried a little different approach and now it says that some rows are affected, but also gives an error and so does not show the result set. I have tried to show it using the dummy data for those two tables. Please, please let me know what's going wrong here?
Please see the details below:
Table 1
usrId usrFirstName usrLastName other fields(...)
1 Adam Cadman ...
1 Adam Cadman ...
2 Dana Jones ...
2 Dana Jones ...
Table2
resultId Result other fields(...)
1 Appt set ...
1 Appt set ...
1 (call back) ...
2 (call back) ...
2 confirm appt ...
3 (call - person - back) ...
3 appt at 6 (1/1/12) ...
Now I want to join these two tables, and want to convert rows into columns using dynamic pivot operation. It should basically give the count of each result for distinct user.
Desired result set
usrFirstName usrLastName Appt set (call back) ...
adam cadman 2 1
dana jones 0 1
.... .... ... ...
When I execute the code below, it gives me the message that certain number of rows are affected, but also gives some error.
Please find everything below:
SELECT DISTINCT Result as 'Result'
INTO #results
FROM table2
DECLARE @resultList nvarchar(max)
SELECT @resultList = COALESCE( @resultList + ', ', '') + CAST( QUOTENAME( Result ) AS VARCHAR(1000) )FROM
#results
ORDER BY Result
--select * from #results
DROP TABLE #results
-- this var will hold the dynamic PIVOT sql
DECLARE @pvt_sql nvarchar(max)
SET @pvt_sql = 'SELECT *
FROM
(SELECT Result, usrFirstName, usrLastName
FROM table1 Inner join table2 on table1.usrId = resultId
) AS data
PIVOT
(
COUNT( Result )
FOR Result IN
( ' + @resultList + ' )
) AS pvt'
-- run the query
EXEC sp_executesql @pvt_sql
------------------------------------
So when I execute this, I get the following:
(384 row(s) affected)
Msg 1038, Level 15, State 4, Line 10
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
i think the problem is that the Result list has entries like (call - back) etc with ( and ) inside. So, is it the delimiters problem in here that is causing this error?
Or is it something related to aliases?
Please help me, i have been struggling with this since many days. Thank you very much in advance.
March 23, 2012 at 4:56 pm
I have a dumb question, why all the dynamic sql when it seems that it all can be run normally (for a better term). I don't see anything in the dynamic sql that calls for it.
March 23, 2012 at 5:00 pm
@Lynn Pettis,
There are 'too many' text values for 'Result' column, and in future I might wanna do it for some other tables, I just dont want to keep editing things...so I'm using dynamic sql query, since i do not know what values will be in there before hand for any other table...
I hope I've answered your question...
March 23, 2012 at 5:11 pm
I can't figure out this part. I see a possible missing single quote (') after field2 on the line prior to the EXECUTE.
Why is this dynamic, there is not editting prior to it being executed.
DECLARE
@Users VARCHAR(2000) = 'SELECT field1, field2, Service, Value
INTO ##myTable2
FROM table1 INNER JOIN table2 ON table1PK = table2FK_table1PK
ORDER BY field1, field2
EXECUTE
(@Users)
SELECT
* FROM ##myTable2 // works fine
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply