x

converting multiple rows into single row

link text

Attaching a image file which will show the exact requirement , Source Table Data which has to be converted into the expected Target Table Data.

Columns are constant i.e. here 16 columns are required in the Target Table.

Kindly let me know what will be the exact logic of converting the data from source table to the target table as required , I have to create a View out of it.

Let me know if any more information is required.

more ▼

asked Oct 09, 2012 at 05:33 PM in Default

Kirman1 gravatar image

Kirman1
181 14 15 18

Hi, there is no attachment...
Oct 09, 2012 at 06:36 PM sp_lock
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Also you can try something along the following lines:

select 
 ID,
 max(case when Code like '%A' then Code else null end) as CodeA,
 max(case when Code like '%A' then Customer else null end) as CustomerA,
 max(case when Code like '%A' then Product else null end) as ProductA,
 max(case when Code like '%A' then Category else null end) as CategoryA,
 max(case when Code like '%B' then Code else null end) as CodeB,
 max(case when Code like '%B' then Customer else null end) as CustomerB,
 max(case when Code like '%B' then Product else null end) as ProductB,
 max(case when Code like '%B' then Category else null end) as CategoryB
from
 SourceTable
group by ID
more ▼

answered Oct 12, 2012 at 04:54 PM

willspurgeon gravatar image

willspurgeon
90 1 1 3

Thanks willspurgeon +1 Vote...
Oct 13, 2012 at 12:30 PM Kirman1
(comments are locked)
10|1200 characters needed characters left

I can't be sure what you need based on the information provided, but I think what you're looking for is called a pivot. Here's the second part of an article on how to write a pivot query. Check it out and see if that's what you're looking for.

more ▼

answered Oct 10, 2012 at 10:41 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1853

asked: Oct 09, 2012 at 05:33 PM

Seen: 1201 times

Last Updated: Oct 13, 2012 at 12:30 PM