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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lleemon
Starting Member

24 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
169 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

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