Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQl Server 2008 table

Posted on 2014-10-07
3
Medium Priority
?
229 Views
Last Modified: 2014-10-10
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
0
Comment
Question by:andrejaTJ
  • 2
2 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40365428
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40365430
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question