Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query urgent

Author  Topic 

chbala85
Starting Member

49 Posts

Posted - 2013-07-29 : 02:53:00
Hi all,

formid questionId question response
444 2170 DeclarantsName jennay
444 2171 Declarantaddress us
444 10200 Ido pain
444 10229 want1 pain2
444 152 want2 pan3
445 2170 DeclarantsName jennay1
445 2171 Declarantaddress us1
445 10200 Ido pain1
445 10229 want1 pain21
445 152 want2 pan31


i have table data like above but need to display below please help me query.............


formid DeclarantsName Declarantaddress Ido want1 want2
4444 jennay us pain pain2 pain3
4445 jennay1 us1 pain1 pain21 pain31


thanks,
krishn..






visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-29 : 03:48:26
[code]
SELECT *
FROM Table t
PIVOT(MAX(response) FOR question IN (DeclarantsName, Declarantaddress, Ido, want1, want2 ))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-29 : 03:52:59
--Alternate is as follows:
SELECT formid
, MAX( CASE WHEN question='DeclarantsName' THEN response END) AS DeclarantsName
, MAX( CASE WHEN question='Declarantaddress' THEN response END) AS Declarantaddress
, MAX( CASE WHEN question='Ido' THEN response END) AS DeclarantsName
, MAX( CASE WHEN question='want1' THEN response END) AS want1
, MAX( CASE WHEN question='want2' THEN response END) AS want2
FROM Table
GROUP BY formid

--
Chandu
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-29 : 04:07:31
For dynamic number of values, http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

chbala85
Starting Member

49 Posts

Posted - 2013-08-06 : 09:37:52
it's not geting my excepted answer.........
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 09:44:28
quote:
Originally posted by chbala85

it's not geting my excepted answer.........



--your Sample data
DECLARE @tab1 TABLE(formid int, questionId int, question varchar(30), response varchar(30))
insert into @tab1
SELECT 444, 2170, 'DeclarantsName', 'jennay' union all
SELECT 444, 2171, 'Declarantaddress', 'us' union all
SELECT 444, 10200, 'Ido', 'pain' union all
SELECT 444, 10229, 'want1', 'pain2' union all
SELECT 444, 152, 'want2', 'pan3' union all
SELECT 445, 2170, 'DeclarantsName', 'jennay1' union all
SELECT 445, 2171, 'Declarantaddress', 'us1' union all
SELECT 445, 10200, 'Ido', 'pain1' union all
SELECT 445, 10229, 'want1', 'pain21' union all
SELECT 445, 152, 'want2', 'pan31'

--Query
SELECT formid
, MAX( CASE WHEN question='DeclarantsName' THEN response END) AS DeclarantsName
, MAX( CASE WHEN question='Declarantaddress' THEN response END) AS Declarantaddress
, MAX( CASE WHEN question='Ido' THEN response END) AS DeclarantsName
, MAX( CASE WHEN question='want1' THEN response END) AS want1
, MAX( CASE WHEN question='want2' THEN response END) AS want2
FROM @tab1
GROUP BY formid
/*OUTPUT:
formid DeclarantsName Declarantaddress DeclarantsName want1 want2
444 jennay us pain pain2 pan3
445 jennay1 us1 pain1 pain21 pan31*/


tell us the exact requirement... As per your explanation & sample data the above query is working fine

--
Chandu
Go to Top of Page
   

- Advertisement -