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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 PIVOT rows to columns with multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lleemon
Starting Member

23 Posts

Posted - 09/06/2013 :  00:27:59  Show Profile  Send lleemon a Yahoo! Message  Reply with Quote
I currently have a demo setup here:
http://sqlfiddle.com/#!3/d41d8/20208

Current data format:
ACCT|REFERENCE_NAME|RELATIONSHIP|REF_ADDR
A2111|Roger Ref|Brother|123 Main St.
A2111|Larry Ref|Uncle|321 Main St.
A2211|Sid Kid|Friend|33 1st St.
4839|Randy Smith|Brother|1 2nd Ave

The output I am trying to get is:
ACCT|REFERENCE_NAME1|RELATIONSHIP1|REF_ADDR1|REFERENCE_NAME2|RELATIONSHIP2|REF_ADDR2|REFERENCE_NAME3|RELATIONSHIP3|REF_ADDR3
A2111|Roger Ref|Brother|123 Main St.|Larry Ref|Uncle|321 Main St.|Sid Kid|Friend|33 1st St
4839|Randy Smith|Brother|1 2nd Ave

I can pivot with one field but not with multiple. Looking for help on how to do that.

Thanks in advance.

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
165 Posts

Posted - 09/10/2013 :  07:48:56  Show Profile  Reply with Quote
SELECT DISTINCT
a.ACCT AS ACCT1
, a.REFERENCE_NAME AS REFERENCE_NAME1
, a.RELATIONSHIP AS RELATIONSHIP1
, a.REF_ADDR AS REF_ADDR1
, b.ACCT AS ACCT2
, b.REFERENCE_NAME AS REFERENCE_NAME2
, b.RELATIONSHIP AS RELATIONSHIP2
, b.REF_ADDR AS REF_ADDR2
, c.ACCT AS ACCT3
, c.REFERENCE_NAME AS REFERENCE_NAME3
, c.RELATIONSHIP AS RELATIONSHIP3
, c.REF_ADDR AS REF_ADDR3
FROM Temp123 AS a
INNER JOIN Temp123 AS b
ON a.ACCT = b.ACCT
INNER JOIN Temp123 AS c
ON a.ACCT = c.ACCT
WHERE a.ACCT = 'A2111'
and a.RELATIONSHIP = 'Brother'
and b.RELATIONSHIP = 'Uncle'
and c.RELATIONSHIP = 'Friend'
UNION all
SELECT ACCT,REFERENCE_NAME,RELATIONSHIP,REF_ADDR,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM Temp123 AS TT
WHERE TT.ACCT = '4839'


veeranjaneyulu

Edited by - VeeranjaneyuluAnnapureddy on 09/10/2013 07:50:30
Go to Top of Page

lleemon
Starting Member

23 Posts

Posted - 09/10/2013 :  08:03:50  Show Profile  Send lleemon a Yahoo! Message  Reply with Quote
Yes, this may work for this example but if I have 1000's of records this will not. Sorry, just posted the small example so didn't have to post a lot of data.

quote:
Originally posted by VeeranjaneyuluAnnapureddy

SELECT DISTINCT
a.ACCT AS ACCT1
, a.REFERENCE_NAME AS REFERENCE_NAME1
, a.RELATIONSHIP AS RELATIONSHIP1
, a.REF_ADDR AS REF_ADDR1
, b.ACCT AS ACCT2
, b.REFERENCE_NAME AS REFERENCE_NAME2
, b.RELATIONSHIP AS RELATIONSHIP2
, b.REF_ADDR AS REF_ADDR2
, c.ACCT AS ACCT3
, c.REFERENCE_NAME AS REFERENCE_NAME3
, c.RELATIONSHIP AS RELATIONSHIP3
, c.REF_ADDR AS REF_ADDR3
FROM Temp123 AS a
INNER JOIN Temp123 AS b
ON a.ACCT = b.ACCT
INNER JOIN Temp123 AS c
ON a.ACCT = c.ACCT
WHERE a.ACCT = 'A2111'
and a.RELATIONSHIP = 'Brother'
and b.RELATIONSHIP = 'Uncle'
and c.RELATIONSHIP = 'Friend'
UNION all
SELECT ACCT,REFERENCE_NAME,RELATIONSHIP,REF_ADDR,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM Temp123 AS TT
WHERE TT.ACCT = '4839'


veeranjaneyulu

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.05 seconds. Powered By: Snitz Forums 2000