Help with Select statement - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Select statement

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2011-10-12 : 02:57:53

I've the following table

Name Type Price1 Price2
----------------------------
ABCD Buy 10 20
ABCD Sell 15 25

I want the output for ABCD in one line as follows

ABCD, Buy, 10, 20, Sell, 15, 25

Any help will be appreciated.

Thanks in advance
Sridhar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-12 : 03:02:13

will you have more than two record for the same Name ?

KH
[spoiler]Time is always against us[/spoiler]

sridhar3004
Starting Member

34 Posts

Posted - 2011-10-12 : 03:05:40

Nope. Only 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 03:07:22

are you trying to export it to csv or something?

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

sridhar3004
Starting Member

34 Posts

Posted - 2011-10-12 : 03:31:15

I want this to be displayed on screen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-12 : 03:34:19

as one column or 7 columns ?

KH
[spoiler]Time is always against us[/spoiler]

sridhar3004
Starting Member

34 Posts

Posted - 2011-10-12 : 03:43:31

as 7 columns

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-12 : 03:50:05

[code]
select [Name],
[Type] = max(case when row_no = 1 then [Type] end),
[Price1] = max(case when row_no = 1 then [Price1] end),
[Price2] = max(case when row_no = 1 then [Price2] end),
[Type] = max(case when row_no = 2 then [Type] end),
[Price1] = max(case when row_no = 2 then [Price1] end),
[Price2] = max(case when row_no = 2 then [Price2] end)
from (
select *, row_no = row_number() over (partition by [Name] order by [Type])
from yourtbl
) t
group by [Name]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 04:56:45

you can extend it dynamically based on number of values present per Name group using below dynamic query

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

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

sridhar3004
Starting Member

34 Posts

Posted - 2011-10-13 : 08:22:52

Thanks guys. It worked.

Warm Regards
Sridhar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources