Column name as row value - 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

General SQL Server Forums

New to SQL Server Programming

Column name as row value

Author

Topic

TorspeR
Starting Member

11 Posts

Posted - 2010-09-27 : 06:33:22

Hello all,
I have a table with users and each user has four different price alternatives. I have already calculated the smallest price among them with (for example oivak_kulut is one of the column names that I need to get):

UPDATE jyy_kaikki_kulut
SET jyy_kaikki_kulut.smallest_price=
CASE
WHEN eisms_kulut <= oivak_kulut AND eisms_kulut <= pro_kulut THEN eisms_kulut
WHEN oivak_kulut <= eisms_kulut AND oivak_kulut <= pro_kulut THEN oivak_kulut
WHEN pro_kulut <= eisms_kulut AND pro_kulut <= oivak_kulut THEN pro_kulut
ELSE eisms_kulut
END
WHERE smspak = 0 AND paketti <> elisaoiva

Now I need to get the cheapest price's column name to be seen in a new column for each user (row) so that I can see which alternative is the cheapest. Feel free to post an answer if you got some kind of solution for me.

Mikko.

Sachin.Nand

2937 Posts

Posted - 2010-09-27 : 07:29:40

Feel free to post an answer if you got some kind of solution for me.

Yeah sure

But need a little more info on how your data & table looks like.

PBUH

TorspeR
Starting Member

11 Posts

Posted - 2010-09-27 : 07:42:40

Yea ok, sorry.

looks something like this (except for smallest_price_column_name column is empty because I don't know how to do this), heh.

user price1 price2 price3 smallest_price smallest_price_column_name
u1 1 4 3 1 price1
u2 3 2 1 1 price3
u3 2 4 1 1 price3

So please help meh!

TorspeR
Starting Member

11 Posts

Posted - 2010-09-27 : 07:44:29

Ok, so SPACE separates the column values here.

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 00:50:52

[code]
declare @tbl as table(id int,users varchar(10),price1 int,price2 int,price3 int)
insert into @tbl
select 1,'u1' ,1, 4, 3 union all
select 2,'u2' ,3, 2, 1 union all
select 3,'u3', 2, 4, 1

select T.id,
T.users,
t1.price1,
t1.price2,
t1.price3,
col smallest_price,
columns smallest_price_column_name
from
(
select *,row_number()over(partition by id order by col)as rid from
(
select * from @tbl
)u
unpivot
(col for columns in (price1,price2,price3))v
)T
inner join @tbl t1 on t1.id=T.id and rid=1

[/code]

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources