SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query urgent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chbala85
Starting Member

49 Posts

Posted - 07/29/2013 :  02:53:00  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/29/2013 :  03:48:26  Show Profile  Reply with Quote

SELECT *
FROM Table t
PIVOT(MAX(response) FOR question IN (DeclarantsName, Declarantaddress, Ido, want1, want2 ))p


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

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 07/29/2013 :  03:52:59  Show Profile  Reply with Quote
--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

India
22754 Posts

Posted - 07/29/2013 :  04:07:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 07/29/2013 04:08:03
Go to Top of Page

chbala85
Starting Member

49 Posts

Posted - 08/06/2013 :  09:37:52  Show Profile  Reply with Quote
it's not geting my excepted answer.........
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 08/06/2013 :  09:44:28  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000