Cross Table

Question

Cross Table

ikrami2000

Default port

Points: 1461
More actions
December 11, 2014 at 8:15 am

#301755

I have this table
Outlet_IDDescription
1Soups
1Salads
1Appetizers
1Tempura
1Tataki
1Teppanyaki
2Teriyaki
2The Sakura Specialty
2Rice
2Noodles
and i need it to be :
Outlet_IDDesc1Desc2 Desc3Desc4
1 SoupsSalads AppetizersTempura
1 TatakiTeppanyaki null null
2 TeriyakiThe Sakura SpecialtyRice Noodles
and store it in a view
the description names it is dynamics

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

ikrami2000 (12/11/2014)
I have this table
Outlet_IDDescription
1Soups
1Salads
1Appetizers
1Tempura
1Tataki
1Teppanyaki
2Teriyaki
2The Sakura Specialty
2Rice
2Noodles
and i need it to be :
Outlet_IDDesc1Desc2 Desc3Desc4
1 SoupsSalads AppetizersTempura
1 TatakiTeppanyaki null null
2 TeriyakiThe Sakura SpecialtyRice Noodles
and store it in a view
the description names it is dynamics

You can't store data in a view. Views are used to retrieve data. That aside what you are doing is quite strange and will require using dynamic sql. That means you can't do this in a view.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

OK this can be done without resorting to dynamic sql but it is kind of ugly. Notice I created the tables and sample data. This is something you should do on future posts. This query returns the results as you stated you want.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

Outlet_ID int

, Description varchar(50)

, SomeKey int identity primary key

)

insert #Something

select 1, 'Soups' union all

select 1, 'Salads' union all

select 1, 'Appetizers' union all

select 1, 'Tempura' union all

select 1, 'Tataki' union all

select 1, 'Teppanyaki' union all

select 2, 'Teriyaki' union all

select 2, 'The Sakura Specialty' union all

select 2, 'Rice' union all

select 2, 'Noodles'

;

with firstPass as

(

select *

, ROW_NUMBER() over(partition by OutLet_ID order by SomeKey) as RowNum

from #Something

)

, MyGroups as

(

select *

, ROW_NUMBER() over(partition by RowNum % 4, Outlet_ID order by SomeKey) as GroupNum

from firstPass

)

select Outlet_ID

, MAX(case when RowNum % 4 = 1 then Description end) as Desc1

, MAX(case when RowNum % 4 = 2 then Description end) as Desc2

, MAX(case when RowNum % 4 = 3 then Description end) as Desc3

, MAX(case when RowNum % 4 = 0 then Description end) as Desc4

from MyGroups

group by Outlet_ID, GroupNum

The technique used here is a cross tab. You can (and should) read more about them by following the links in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ikrami2000 Default port Points: 1461 More actions · Answer 3

Thank you it's working, but let me ask if this will work for any number of records ?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

ikrami2000 (12/11/2014)
Thank you it's working, but let me ask if this will work for any number of records ?

Sure it will work with more rows. Glad it is working for you. Make sure you fully understand what that code is doing since you will have to support it. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/