June 11, 2014 at 9:14 am
Hi I want to pivot a table something like this .
I pivotted it succesfully but the results are not correct
Here is the example :
install-Name Fiscal year Question Answer
Washington 2010 what is the reason for install? tttttt ggg yttt o
washington 2010 reason id 12345
washington 2010 install start date 10/10/2010
washington 2010 install end date 10/12/2010
washington 2010 install status successfull
washington 2010 what is the reason for install? trtbnbthwgt hrgthjrt
washington 2010 reason id -
washington 2010 install start date 12/3/2010
washington 2010 install end date 12/8/2010
washington 2010 install status
washington 2011 what is the reason for install? sbjeh dhebwdh dbjw
washington 2011 reason id 345
washington 2011 install start date 12/3/2011
washington 2011 install end date 12/8/2011
washington 2011 install status successfull
washington 2011 Do you feel the install is incomplete? No
washington 2011 Is the expiration of 90 days exceeded? yes,but b b b b
CREATE TABLE #myQuestionTable
(
InstallationName NVARCHAR(MAX),
FiscalYear INT,
Question NVARCHAR(MAX),
Answer NVARCHAR(MAX)
)
INSERT INTO #myQuestionTable ( InstallationName, FiscalYear,Question,Answer)
Values ('washington,2010,'what is the reason for install?','tttttt ggg yttt o'),
('washington',2010, 'reason id','12345'),
('washington',2010,'install start date','10/10/2010'),
('washington',2010,'install end date','10/12/2010'),
('washington',2010,'install status','successfull'),
('washington',2010,'what is the reason for install?','trtbnbthwgt hrgthjrt'),
('washington',2010, 'reason id','-'),
('washington',2010,'install start date', '12/3/2010'),
('washington',2010,'install end date', '12/8/2010'),
('washington',2010,'install status'),
('washington',2011,'what is the reason for install?','sbjeh dhebwdh dbjw'),
('washington',2011, 'reason id',345),
('washington',2011,'install start date', '12/3/2011'),
('washington',2011,'install end date', '12/8/2011'),
('washington',2011,'install status', 'successfull'),
('washington',2011,'Do you feel the install is incomplete?','No'),
('washington',2011,'Is the expiration of 90 days exceeded?','yes,but b b b b')
I want the above data to get pivoted like this
Install-name | Fiscal year |what is the reason for install? | reason id | install start date | install end date |
install status |Do you feel the install is incomplete? | Is the expiration of 90 days exceeded? |
washington | 2010 | tttttt ggg yttt o | 12345 | 10/10/2010 | 10/12/2010 |
successful | | |
washington | 2010 | trtbnbthwgt hrgthjrt | - | 12/3/2010 | 12/8/2010 |
| | |
washington | 2011 | sbjeh dhebwdh dbjw | 345 | 10/10/2010 | 10/12/2010 |
successful | No | Yes ,but b b b b |
please help .i have been trying it since a week
Thanks,
sravz
June 11, 2014 at 9:17 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2014 at 4:11 am
hi sravanthi,
here is the query
select InstallationName
,FiscalYear
,[what is the reason for install?]
,[reason id]
,[install start date]
,[install end date]
,[install status]
,[Do you feel the install is incomplete?]
,[Is the expiration of 90 days exceeded?]
from (select
InstallationName
,FiscalYear
,Question
,Answer
From myQuestionTable ) P PIVOT (Max(answer) for Question in
([what is the reason for install?]
,[reason id]
,[install start date]
,[install end date]
,[install status]
,[Do you feel the install is incomplete?]
,[Is the expiration of 90 days exceeded?])) as PVT
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply