this is the first-time I use this and I hope i will achieve this, I am getting mad. I am still a newbie about all this programming with SQL thing, I am trying to use pivot table to turn the source table in the desired output showed as below.
This is my source table
Here is the image, I was unable to upload it (https://i.sstatic.net/KDStF.jpg).
But whatever I try it returns me numbers but the varchar values I would like to, as it is shown above. Would 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
MS SQL Server 2008 Schema Setup:
Query 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
| DOCID | QUES1 | QUES2 |
|-------|-------|-------|
| 1 | Hola | Padr |
| 2 | Excue | Dir |