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.
| Author |
Topic |
|
lleemon
Starting Member
24 Posts |
 Posted - 2013-09-06 : 00:27:59
|
| I currently have a demo setup here:[url]http://sqlfiddle.com/#!3/d41d8/20208[/url]Current data format:ACCT|REFERENCE_NAME|RELATIONSHIP|REF_ADDRA2111|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 AveThe output I am trying to get is:ACCT|REFERENCE_NAME1|RELATIONSHIP1|REF_ADDR1|REFERENCE_NAME2|RELATIONSHIP2|REF_ADDR2|REFERENCE_NAME3|RELATIONSHIP3|REF_ADDR3A2111|Roger Ref|Brother|123 Main St.|Larry Ref|Uncle|321 Main St.|Sid Kid|Friend|33 1st St4839|Randy Smith|Brother|1 2nd AveI can pivot with one field but not with multiple. Looking for help on how to do that.Thanks in advance. |
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-09-10 : 07:48:56
|
| 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 |
 |
|
|
lleemon
Starting Member
24 Posts |
Posted - 2013-09-10 : 08:03:50
|
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
|
|
|
|
|
|
|
|
|
PIVOT rows to columns with multiple columns