0

Hello I am having two tables and I want to generate the report where all the rows will be in single line.

tbl_Questions:

Id  Name
---------
1   Abc
2   Cde
3   xyz
4   MMM

tbl_Reponses:

Id  QuestionId   Values
------------------------
1      1           123
2      2           456
3      3           444

Now I want to get a query which will show sometime like this in result.

Id Abc Cde xyz MMM
-------------------
1  123 456 444

How can I get this through a query ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Moiz
  • 2,409
  • 5
  • 27
  • 50
  • [Transpose rows into columns in SQL Server 2008 R2](http://stackoverflow.com/questions/20119162/transpose-rows-into-columns-in-sql-server-2008-r2) [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – One-One Feb 25 '15 at 05:41
  • Thanks. Is there any way to directly do that in SSRS ? because if I convert this through pivot, how I will assign the dynamic columns in table ? – Moiz Feb 25 '15 at 05:44
  • I am not sure, Use column /Row grouping to achieve that – Habeeb Feb 25 '15 at 05:47
  • I got that directly in SSRS Report through matrix. Thanks – Moiz Feb 25 '15 at 05:49

2 Answers2

0

Use PIVOT to do that:

SELECT ROW_NUMBER() OVER(ORDER BY [Abc])Id,* FROM
(
SELECT Name,Value FROM tbl_Questions A LEFT JOIN tbl_Reponses B ON A.Id=B.Id
) AS tbl
PIVOT(SUM(Value) FOR Name IN([Abc],[Cde],[xyz],[MMM]))AS pvt

Or the CrossTab Method

SELECT
    ROW_NUMBER() OVER (ORDER BY MAX(A.Id))Id,
    [Abc] = MAX(CASE WHEN Name = 'Abc' THEN Value END),
    [Cde] = MAX(CASE WHEN Name = 'Cde' THEN Value END),
    [xyz] = MAX(CASE WHEN Name = 'xyz' THEN Value END),
    [MMM] = MAX(CASE WHEN Name = 'xyz' THEN Value END)
FROM tbl_Questions A LEFT JOIN tbl_Reponses B ON A.Id=B.Id
Rigel1121
  • 2,022
  • 1
  • 17
  • 24
0

using Group by

select 
ID
, MIN(CASE WHEN A.NAME ='ABC'
THEN B.VALUES
END) ABC
, MIN(CASE WHEN A.NAME ='cde'
THEN B.VALUES
END) cde
, MIN(CASE WHEN A.NAME ='xyz'
THEN B.VALUES
END) xyz
, ISNULL(MIN(CASE WHEN A.NAME ='mmm'
THEN B.VALUES
END),'') mmm
FROM
tbl_Questions A
inner join tbl_Reponses B
on A.ID=B.ID
group by A.ID
SoulTrain
  • 1,904
  • 1
  • 12
  • 11