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 for one column to multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajsqlteam
Starting Member

14 Posts

Posted - 12/03/2012 :  10:55:38  Show Profile  Reply with Quote
Hello experts,

I am new to SQL programming. I am trying to query a table and get a result as shown below. Kindly provide your feedback.
Source table: (Line is a column stating number of lines person ID consists of)
ID AGE LINE Address
1 23 1 238 Wa AVE
2 21 1 234 Some st
3 45 1 776 oth st
1 23 2 Washington

Desired output: (ID should not repeat, instead add a new column address 2 in the same row)
ID AGE Adress 1 Address 2
1 23 238 Wa AVE Washington
2 21 234 Some st Null
3 45 776 other st Null


Pardon for not showing in tabular format. This is my first post!



Thanks.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/03/2012 :  10:58:13  Show Profile  Reply with Quote

SELECT ID,
 AGE,
 MAX(CASE WHEN LINE = 1 THEN Address END) AS Address1,
 MAX(CASE WHEN LINE = 2 THEN Address END) AS Address2
FROM table
GROUP BY ID,
AGE


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/03/2012 :  11:08:40  Show Profile  Reply with Quote
Thanks much. :)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/03/2012 :  11:11:50  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/03/2012 :  11:34:38  Show Profile  Reply with Quote
Have another question related to this.

Lets say we dont have the Line column in the source table, How can we get both adresses in the result set?
Source Table:
ID AGE Address
1 23 238 Wa AVE
2 21 234 Some st
3 45 776 oth st
1 23 Washington

Output:
ID AGE Adress 1 Address 2
1 23 238 Wa AVE Washington
2 21 234 Some st Null
3 45 776 other st Null
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/03/2012 :  11:35:48  Show Profile  Reply with Quote
do you've any other unique valued column to determine the order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/03/2012 :  11:46:41  Show Profile  Reply with Quote
Lets say order doesn't matter here..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/03/2012 :  12:07:24  Show Profile  Reply with Quote
then this would be fine


SELECT ID,
 AGE,
 MAX(CASE WHEN LINE = 1 THEN Address END) AS Address1,
 MAX(CASE WHEN LINE = 2 THEN Address END) AS Address2
FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID,AGE ORDER BY Address) AS LINE
FROM table
)t
GROUP BY ID,
AGE


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/04/2012 :  09:48:30  Show Profile  Reply with Quote
Thank you very much!

But Let me be more clear on what exactly I am clicking.
I actually have two tables where ID is tied to a value as shown below,

ID value
1000 1
1002 1
1004 2
1000 3
1000 2
1002 2

So when I have a value of 1 for an ID it should show up 1, and for the same ID if I have a value of 2 it should show up as 1 else 0.
RESULT SET:

ID value1 value2 value3
1000 1 1 1
1002 1 1 0
1004 0 1 0

Please let me know.

Thanks



Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/04/2012 :  11:18:26  Show Profile  Reply with Quote
You will have to see Dynamic SQL. There are plenty of post on it. How you determine Value will go to Value1 or Value2 ........
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/04/2012 :  11:47:54  Show Profile  Reply with Quote
WOuld depend on now many Values you expect. If you need code to work for N number of different values, then Sodeep is correct that you'd need Dynamic SQL. If it is a finite set, then you can write SQL easy enough to do that.
Go to Top of Page

rajsqlteam
Starting Member

14 Posts

Posted - 12/04/2012 :  11:51:41  Show Profile  Reply with Quote
Thanks Sodeep and Lamprey!

@Lamprey

Yes the number of values I need is finite. As in the exmaple I just need 3, could you please let me know the sql?

Thanks
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/04/2012 :  12:21:29  Show Profile  Reply with Quote
Select ID,
Case When CNT > 0 then 1 else 0 end as Value1,
Case When CNT > 1 and CNT <= 2 then 1 else 0 end as Value2,
Case When CNT > 2 and CNT <= 3 then 1 else 0 end as Value3
from
(
Select ID,Count(*) CNT
from Table
Group by ID
)P
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.08 seconds. Powered By: Snitz Forums 2000