Sql server SubQuery (Network Steve Forum)

Sql server SubQuery

Hi ,

I have table with below format.

VPID	VALUE
1	0.95
1	0.895
1	0.96
1	0.96
1	0.94
1	0.95
1	0.945
1	0.955
1	0.955
1	0.96
2	0.486
2	0.486
2	0.486
2	0.486
2	0.486
2	0.486
2	0.486
2	0.486
2	0.486
2	0.486
3	0.37
3	0.325
3	0.375
3	0.395
3	0.36
3	0.38
3	0.365
3	0.37
3	0.365
3	0.37
4	0.105
4	0.105
4	0.1
4	0.095
4	0.085
4	0.095
4	0.08
4	0.1
4	0.095
4	0.095

I want to display the above table as

wt1	wt2	wt3	wt4
0.95	0.486	0.37	0.105
0.895	0.486	0.325	0.105
0.96	0.486	0.375	0.1
0.96	0.486	0.395	0.095
0.94	0.486	0.36	0.085
0.95	0.486	0.38	0.095
0.945	0.486	0.365	0.08
0.955	0.486	0.37	0.1
0.955	0.486	0.365	0.095
0.96	0.486	0.37	0.095

Where wt1 is the values of VPID 1 and wt2 is VPID 2 etc....

Any Suggest

July 21st, 2015 9:57am

You need to PIVOT the rows groups into difefrent Columns.

Check this post: http://sqlwithmanoj.com/2009/04/12/ms-sql-server-2005-new-feature-pivot-and-unpivot/

Free Windows Admin Tool Kit Click here and download it now

July 21st, 2015 10:08am

Do you have another column that controls the order within the group or just the order or rows as you showed them? Say, some Id column that provides the order shown?

If yes, then

;with cte as (select VPID, Value, ROW_NUMBER() over (partition by VPID order by ID) as Rn

from DataFile)

select [1] as wt1, [2] as wt2, [3] as wt3, [4] as wt4 from cte PIVOT (max(Value) for VPID in

([1],[2],[3],[4])) pvt

July 21st, 2015 10:58am

Thanks working fine.

Free Windows Admin Tool Kit Click here and download it now

July 21st, 2015 11:38pm

This topic is archived. No further replies will be accepted.