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
 Pivot one of the table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ugh3012
Yak Posting Veteran

62 Posts

Posted - 12/24/2013 :  10:23:35  Show Profile  Reply with Quote
I have to pivot one table to get the result in correct format. Here is an example as I am not sure how to explain it. If there is another way instead of pivot, let me know.


Table1
ID | Name |
1  | Joe  |
2  | Ron  |

Table2
Table1_ID  | Language 
1          | English
1          | ASL
2          | Spanish
2          | English
2          | French


I need the two tables joined and table2 to be pivoted to get the desired result as shown below. Third column for language is left off as I am limited to two columns.


Result
ID  | Name | Language 1| Language 2
1   | Joe  | English   | ASL
2   | Ron  | Spanish   | English 

Edited by - ugh3012 on 12/24/2013 10:24:46

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 12/24/2013 :  12:40:53  Show Profile  Reply with Quote

SELECT ID,Name,[1] AS Language1,[2] AS Language2
FROM
(
SELECT t1.ID,Name,[Language],Rn
FROM Table1 t1
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY table1_ID ORDER BY [Language]) AS Rn,*
FROM Table2)t2
ON t2.Table1_ID  = t1.ID
AND t2.Rn <=2
)m
PIVOT (MAX([Language]) FOR Rn IN ([1],[2]))p


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

cgraus
Starting Member

Australia
12 Posts

Posted - 12/27/2013 :  05:06:38  Show Profile  Reply with Quote
Note that one of your users has three values, and both the pivot solution, and the one I am offering, will only find as many values as you write code for. Here's a way of doing it without pivots.

SELECT id, name,
MAX(CASE WHEN t.row = 1 THEN language ELSE NULL END) as language1,
MAX(CASE WHEN t.row = 2 THEN language ELSE NULL END) as language2,
MAX(CASE WHEN t.row = 3 THEN language ELSE NULL END) as language3
FROM
(
select id, name, language, row_number() OVER (partition by id order by name) as row
FROM table1 t1 inner join table2 t2 on t1.id = t2.table1_id
) t
GROUP BY id, name
Go to Top of Page

cgraus
Starting Member

Australia
12 Posts

Posted - 12/27/2013 :  05:09:10  Show Profile  Reply with Quote
And here's a better way. This uses FOR XML to build a comma seperated list. It will work for any number of records for the same user.

SELECT id, name,
STUFF((Select ',' + CAST(language AS VARCHAR(1000))
FROM table2 t2
WHERE t1.id = t2.table1_id
FOR XML PATh ('')
),1,1,'')
from table1 t1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 12/28/2013 :  01:52:38  Show Profile  Reply with Quote
quote:
Originally posted by cgraus

And here's a better way. This uses FOR XML to build a comma seperated list. It will work for any number of records for the same user.

SELECT id, name,
STUFF((Select ',' + CAST(language AS VARCHAR(1000))
FROM table2 t2
WHERE t1.id = t2.table1_id
FOR XML PATh ('')
),1,1,'')
from table1 t1



This will just give all language values inside same column which is not what OP is asking for

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

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 12/28/2013 :  01:54:23  Show Profile  Reply with Quote
quote:
Originally posted by cgraus

Note that one of your users has three values, and both the pivot solution, and the one I am offering, will only find as many values as you write code for. Here's a way of doing it without pivots.

SELECT id, name,
MAX(CASE WHEN t.row = 1 THEN language ELSE NULL END) as language1,
MAX(CASE WHEN t.row = 2 THEN language ELSE NULL END) as language2,
MAX(CASE WHEN t.row = 3 THEN language ELSE NULL END) as language3
FROM
(
select id, name, language, row_number() OVER (partition by id order by name) as row
FROM table1 t1 inner join table2 t2 on t1.id = t2.table1_id
) t
GROUP BY id, name



Even if you want to extend it for unknown number of values you can use methods like below which will obviously involve dynamic sql

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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