December 14, 2016 at 1:51 pm
hi
I have a column
QuestionText response
Firstname John
Lastname Bradley
City Irvine
I need
FirstName LAstName City
John bradley Irvine
How can we do it?
December 14, 2016 at 2:07 pm
Search for CROSS TABS and Pivots.
Here's an article that might help: http://www.sqlservercentral.com/articles/T-SQL/63681/
Also search read about EAV and why this design is a bad idea. Then change it if possible.
December 14, 2016 at 2:19 pm
I have written code , but it throws error.
SELECT textresponse
,[Last Name] as LastName
,[First Name] as Firstname
[City]
FROM
(
Select
textresponse AS textresponse
,questiONtext
From #T
)AS P
PIVOT(
Max(textresponse) FOR [questiontext] in ([Last Name],[First Name],[City])
)as Pvt
December 14, 2016 at 2:34 pm
komal145 (12/14/2016)
I have written code , but it throws error.SELECT textresponse
,[Last Name] as LastName
,[First Name] as Firstname
[City]
FROM
(
Select
textresponse AS textresponse
,questiONtext
From #T
)AS P
PIVOT(
Max(textresponse) FOR [questiontext] in ([Last Name],[First Name],[City])
)as Pvt
Textresponse is the column being aggregated, so it is not available in the main query. You also cannot return the pivot column (questiontext).
You should learn to use the IFCode Shortcuts that are on the left when you are posting.
-- removed textresponse
SELECT [Last Name] as LastName
,[First Name] as Firstname
[City]
FROM
(
Select
textresponse AS textresponse
,questiONtext
From #T
)AS P
PIVOT(
Max(textresponse) FOR [questiontext] in ([Last Name],[First Name],[City])
)as Pvt
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 14, 2016 at 3:41 pm
testing iFcode
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply