SQL Server Performance Forum – Threads Archive

Display Rows as Columns

Hi,
I need some help here. I have a view showing the below output.
—————————————————————————————
SurId SurKey RId Qs_Code Qs_Text ScaleText Res_Text Ans_text QuesId ScaleId
—————————————————————————————
1 ZER 45 X1P1Q1 Company Kanbay Null 999 1501
1 RES 46 X1P1Q1 Company Capgem Null 999 1501
1 SWE 45 X1P2Q1 Currency null USD 1000 1602
1 WER 46 X1P2Q1 Currency null CAN 1000 1602.
I want output like the below.Could someone help me with the SQL query.
SurKey RId Company Currency
————————————————————-
1 45 Kanbay USD
1 46 Capgem CAN

---

Look for Cross-Tab reports in BOL. It will help you.
Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

declare @table table
(
SurId int,
SurKey varchar(10),
RId int,
Qs_Code varchar(10),
Qs_Text varchar(10),
–ScaleText varchar(10),
Res_Text varchar(10),
Ans_text varchar(10),
QuesId int,
ScaleIdint
)
insert into @table
select1, ‘ZER’, 45, ‘X1P1Q1’, ‘Company’, ‘Kanbay’, Null, 999, 1501 union all
select1, ‘RES’, 46, ‘X1P1Q1’, ‘Company’, ‘Capgem’, Null, 999, 1501 union all
select1, ‘SWE’, 45, ‘X1P2Q1’, ‘Currency’, null, ‘USD’, 1000, 1602 union all
select1, ‘WER’, 46, ‘X1P2Q1’, ‘Currency’, null, ‘CAN’, 1000, 1602 selectSurId, RId,
[Company] = max(case when Qs_Text = ‘Company’ then Res_Text end),
[Currency] = max(case when Qs_Text = ‘Currency’ then Ans_text end)
[email protected]
group by SurId, RId /*
SurId RId Company Currency
———– ———– ———- ———-
1 45 Kanbay USD
1 46 Capgem CAN
*/ KH

---

]]>