Multiple Rows into One

ISBN Parent_ISBN Title FMonth Forecast SQL TABLE 043967XXXX 043967XXXX MY BOOK 200705 13.25 043967XXXX 043967XXXX MY BOOK 200706 88 043967XXXX 043967XXXX MY BOOK 200707 53 043967XXXX 043967XXXX MY BOOK 200708 54 043967XXXX 043967XXXX MY BOOK 200709 8.08 043967XXXX 043967XXXX MY BOOK 200710 11.84 043967XXXX 043979XXXX MY BOOK2 200705 6.5 043967XXXX 043979XXXX MY BOOK2 200706 135 043967XXXX 043979XXXX MY BOOK2 200707 82 043967XXXX 043979XXXX MY BOOK2 200708 83 043967XXXX 043979XXXX MY BOOK2 200709 80.64 043967XXXX 043979XXXX MY BOOK2 200710 112.16 ISBN Parent_ISBN Title FMonth Forecast FMonth Forecast FMonth Forecast FMonth Forecast FMonth Forecast FMonth Forecast 043967XXXX 043967XXXX MY BOOK 200705 13.25 200706 88 200707 53 200708 54 200709 8.08 200710 11.84 043967XXXX 043979XXXX MY BOOK2 200705 6.5 200706 135 200707 82 200708 83 200709 80.64 200710 112.16 (Desired) RESULTS

I have a table with multiple rows per ISBN. I would like to create a table with one line per ISBN adding columns. I dont want to concatenate any columns.

My goal is to dump this into a spreadsheet through Intregration Services 2005. I am not the DBA so I can't redesign the main table. Thanks for any help.

Here you go..

Code Snippet

--Drop table #books

Create Table #books (

[ISBN] Varchar(100) ,

[Parent_ISBN] Varchar(100) ,

[Title] Varchar(100) ,

[FMonth] Varchar(100) ,

[Forecast] Varchar(100)

);

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200705','13.25');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200706','88');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200707','53');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200708','54');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200709','8.08');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200710','11.84');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200705','6.5');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200706','135');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200707','82');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200708','83');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200709','80.64');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200710','112.16');

Select

[ISBN]

,[Parent_ISBN]

,[Title]

,Max(Case [FMonth] When 200705 Then 200705 End) as FMonth

,Max(Case [FMonth] When 200705 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200706 Then 200706 End) as FMonth

,Max(Case [FMonth] When 200706 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200707 Then 200707 End) as FMonth

,Max(Case [FMonth] When 200707 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200708 Then 200708 End) as FMonth

,Max(Case [FMonth] When 200708 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200709 Then 200709 End) as FMonth

,Max(Case [FMonth] When 200709 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200710 Then 200710 End) as FMonth

,Max(Case [FMonth] When 200710 Then Forecast End) as Forecast

From

#books

Group By

[ISBN]

,[Parent_ISBN]

,[Title]

|||The only problem with this is that I have several thousand ISBNs which all appear 6 times in the table. From each set of 6 rows I need to create one row. Is there anyway that I can do this by setting parameters? Thanks|||

Yes. You can . here it is..

Code Snippet

--Drop table #books

Create Table #books (

[ISBN] Varchar(100) ,

[Parent_ISBN] Varchar(100) ,

[Title] Varchar(100) ,

[FMonth] Varchar(100) ,

[Forecast] Varchar(100)

);

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200705','13.25');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200706','88');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200707','53');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200708','54');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200709','8.08');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200710','11.84');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200705','6.5');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200706','135');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200707','82');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200708','83');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200709','80.64');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200710','112.16');

Declare @.Start as Varchar(10);

Set @.Start = '200707'

Select

[ISBN]

,[Parent_ISBN]

,[Title]

,@.Start as FMonth

,Max(Case [FMonth] When @.Start Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,1,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,1,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,2,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,2,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,3,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,3,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,4,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,4,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,5,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,5,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

From

#books

Where

[FMonth] Between @.Start And Substring(Convert(varchar,DateAdd(MM,5,Cast(@.Start + '01' as datetime)),112),1,6)

Group By

[ISBN]

,[Parent_ISBN]

,[Title]

|||

For 2005:

Code Snippet

Droptable #books

CreateTable #books (

[ISBN] Varchar(100),

[Parent_ISBN] Varchar(100),

[Title] Varchar(100),

[FMonth] Varchar(100),

[Forecast] money

);

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200705','13.25');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200706','88');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200707','53');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200708','54');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200709','8.08');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200710','11.84');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200705','6.5');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200706','135');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200707','82');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200708','83');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200709','80.64');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200710','112.16');

;with cte as

(

select isbn +'-'+ parent_isbn as rkey, forecast, row_number()over(partitionby isbn, parent_isbn orderby fmonth)as rn

from #books

), cte2 as

(

select rkey, [1] as F1, [2] as F2, [3] as F3, [4] as F4, [5] as F5, [6] as F6

from cte

pivot

(

sum(forecast)

FOR rn IN([1], [2], [3], [4], [5], [6])

)as p

), cte3 as

(

select isbn +'-'+ parent_isbn as rkey, fmonth, row_number()over(partitionby isbn, parent_isbn orderby fmonth)as rn

from #books

), cte4 as

(

select rkey, [1] as FM1, [2] as FM2, [3] as FM3, [4] as FM4, [5] as FM5, [6] as FM6

from cte3

pivot

(

min(fmonth)

FOR rn IN([1], [2], [3], [4], [5], [6])

)as p

)

select books.isbn, books.parent_isbn, books.title,

cte4.fm1 as FMonth, cte2.f1 as Forecast,

cte4.fm2 as FMonth, cte2.f2 as Forecast,

cte4.fm3 as FMonth, cte2.f3 as Forecast,

cte4.fm4 as FMonth, cte2.f4 as Forecast,

cte4.fm5 as FMonth, cte2.f5 as Forecast,

cte4.fm6 as FMonth, cte2.f6 as Forecast

from cte2

innerjoin cte4

on cte2.rkey = cte4.rkey

innerjoin

(selectdistinct isbn, parent_isbn, title

from #books books

)as books

onleft(cte2.rkey,charindex('-', cte2.rkey)-1)= books.isbn

andright(cte2.rkey,len(cte2.rkey)-charindex('-', cte2.rkey))= books.parent_isbn

|||This was exactly what i was looking for...thanks...the only problem was having a static date in:

Declare @.Start as Varchar(10);

Set @.Start = '200707'

I wanted to use current yyyymm.