Pivot table with varchar values
This is the first time I use this and I hope I can achieve it, I'm angry. I'm still new to all this programming with SQL stuff, I am trying to use a pivot table to turn the original table into the desired output as shown below.
This is my original table
Here is an image, I was unable to download it ( http://i.stack.imgur.com/KDStF.jpg ).
But everything I've tried is returning me numbers, but the varchar values I would like to do as shown above. Does any of you know how to achieve it?
I am using Transact SQL in Sql Server, here is my code
SELECT [Ques1], [Ques2]
FROM
(
SELECT
D.DocumentId,
DA.QuestionId,
Q.ShortText,
DA.[Text],
Q.SectionId,
1 as [Count]
-- ShortText is literally 'Ques1' or 'Ques2'
FROM Document D
INNER JOIN DocumentAnswer DA
ON DA.DocumentId = D.DocumentId
INNER JOIN Question Q
ON Q.QuestionId = DA.QuestionId
WHERE D.DeleteDate IS NULL
) d
PIVOT
(
Max(ShortText)
FOR [Text] IN ([Ques1], [Ques2])
) p
+3
source to share
1 answer
Configuring MS SQL Server 2008 schema :
Request 1 :
DECLARE @TABLE TABLE (DocID INT, Ques VARCHAR(100), Ans VARCHAR(100))
INSERT INTO @TABLE VALUES
(1 , 'Ques1' , 'Hola'),
(1 , 'Ques2' , 'Padr'),
(2 , 'Ques1' , 'Excue'),
(2 , 'Ques2' , 'Dir')
SELECT * FROM
( -- Put your existing query here
SELECT * FROM @TABLE
) t
PIVOT (MAX(Ans)
FOR Ques
IN ([Ques1],[Ques2])
)p
Results :
| DOCID | QUES1 | QUES2 |
|-------|-------|-------|
| 1 | Hola | Padr |
| 2 | Excue | Dir |
+1
source to share