SQl Server 2008 table

Hi,
I have a table:

10	1
10	2
10	3
110	1
110	2
110	3
360	1
360	2
360	3
470	1
470	2
470	3
560	1
560	2
560	3
760	1
760	2
760	3
1180	1
1180	2
1180	3
1390	1
1390	2
1390	3
1690	1
1690	2
1690	3
2520	1
2520	2
2520	3
2900	1
2900	2
2900	3
2930	1
2930	2
2930	3
3380	1
3380	2
3380	3
6970	1
6970	2
6970	3
7170	1
7170	2
7170	3
7230	1
7230	2
7230	3
7830	1
7830	2
7830	3
8040	1
8040	2
8040	3
9050	1
9050	2
9050	3
10500	1
10500	2
10500	3
11450	1
11450	2
11450	3
11450	6
11900	1
11900	2
11900	3
11900	6
12320	1
12320	2
12320	3
12320	6
13650	6
14220	6
14710	6
14870	6
15520	1
15520	2
15520	3
15770	1
15770	2
15770	3
15800	1
15800	2
15800	3
15840	1
15840	2
15840	3
15850	1
15850	2
15850	3
15900	1
15900	2
15900	3
18760	4
18760	5
18760	6
18970	4
18970	5
18970	6
19020	4
19020	5
19020	6
19160	4
19160	5
19160	6
19310	4
19310	5
19310	6
19390	4
19390	5
19390	6
19440	4
19440	5
19440	6
19570	4
19570	5
19570	6
19680	4
19680	5
19680	6
19850	4
19850	5
19850	6
20260	4
20260	5
20500	4
20500	5
20500	6
20760	4
20760	5
20760	6
20810	4
20810	5
20810	6
20860	4
20860	5
20860	6
20950	4
20950	5
20950	6
20960	4
20960	5
20960	6
21010	4
21010	5
21010	6
21020	4
21020	5
21020	6
21060	4
21060	5
21060	6
21120	4
21120	5
21120	6
21340	4
21340	5
21410	4
21410	5
21430	4
21430	5
21440	4
21440	5
21450	4
21450	5
21460	4
21460	5
21550	4
21550	5
22120	4
22120	5
22150	4
22150	5
22260	4
22260	5
22310	4
22310	5
22430	4
22430	5
22670	4
22670	5
23170	4
23170	5
23200	4
23200	5
23280	4
23280	5
23480	4
23480	5
23510	4
23510	5
23560	4
23560	5
23570	4
23570	5
23640	4
23640	5
23700	4
23700	5
23730	4
23730	5
23740	4
23740	5
23930	4
23930	5
23970	4
23970	5
23990	4
23990	5
24000	4
24000	5
24010	4
24010	5
24020	4
24020	5
24030	4
24030	5
24040	4
24040	5
24050	4
24050	5
24060	4
24060	5
24070	4
24070	5
24080	4
24080	5
24090	4
24090	5
24100	4
24100	5
24120	4
24120	5
24130	4
24130	5
24160	4
24160	5
24200	4
24200	5

Open in new window


I need a view in the format:
10        1  2  3
110	      1  2  3
.
.
.

24010   4  5

Open in new window


TX
andrejaTJAsked:
Who is Participating?
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you don't want gaps under columns 1, 2 and 3 for the later rows, then use:

with mynewtable as
(select field1, field2, row_number() over(partition by field1 order by field2) as field3
from mytable)
select field1, isnull([1],0) as [1], isnull([2],0) as [2], isnull([3],0) as [3]
from mynewtable
pivot (sum([field2]) for field3 in ([1],[2],[3])) as pvt

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
with mynewtable as
(select field1, field2
from mytable)
select field1, isnull([1],0) as [1], isnull([2],0) as [2], isnull([3],0) as [3], isnull([4],0) as [4], isnull([5],0) as [5], isnull([6],0) as [6]
from mynewtable
pivot (sum([field2]) for field2 in ([1],[2],[3],[4],[5],[6])) as pvt

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.