Pivot Table with varchar values

pivot, sql-server, t-sql, varchar

Solution

SQL Fiddle

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

Results:

| DOCID | QUES1 | QUES2 |
|-------|-------|-------|
|     1 |  Hola |  Padr |
|     2 | Excue |   Dir |

Problem

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.stack.imgur.com/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 ```

Original source