Help Needed in cross logic Query

Question

Help Needed in cross logic Query

KGJ-Dev

SSCertifiable

Points: 5780
More actions
September 3, 2015 at 9:22 pm

#398996

Hi,
Below is my sample data to play with,
Declare @Store table(id int primary key identity(1,1),StoreId int);
Declare @Message table(StoreId int, Messages varchar(100));
Insert into @Store(StoreId)
select 100 union all select 200 union all select 300 union all select 400 union all select 500;
Insert into @Message(StoreId,Messages)
select 100, 'Message1' union all
select 100, 'Message2' union all
select 100, 'Message3' union all
select 100, 'Message4' union all
select 100, 'Message5' union all
select 200, 'Message1' union all
select 200, 'Message2' union all
select 200, 'Message3' union all
select 200, 'Message4' union all
select 200, 'Message5'
--Required output:
select 100 as StoreId,'Message1' as M1,'Message2' as M2,'Message3'as M3,'Message4' as M4,'Message5' as M5 union all
select 200 as StoreId,'Message1' as M1,'Message2' as M2,'Message3'as M3,'Message4' as M4,'Message5' as M5
Should i use rows to columns conversion logic or should i use cross appy. Any suggestions and any sample qury how to achieve this logic.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

pivot makes sense if the message column is a int or numerical data type on which aggregations need to be performs else if the number of messages is static you can use xml to pivot the data for you.

Jayanth Kurup[/url]

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Quick cross-tab suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

Declare @Store table(id int primary key identity(1,1),StoreId int);

Declare @Message table(StoreId int, Messages varchar(100));

Insert into @Store(StoreId)

select 100 union all select 200 union all select 300 union all select 400 union all select 500;

Insert into @Message(StoreId,Messages)

select 100, 'Message1' union all

select 100, 'Message2' union all

select 100, 'Message3' union all

select 100, 'Message4' union all

select 100, 'Message5' union all

select 200, 'Message1' union all

select 200, 'Message2' union all

select 200, 'Message3' union all

select 200, 'Message4' union all

select 200, 'Message5';

;WITH BASE_DATA AS

(

SELECT

ST.StoreId

,ROW_NUMBER() OVER

(

PARTITION BY ST.StoreId

ORDER BY MSG.[Messages]

) AS ST_RID

,MSG.[Messages]

FROM @Store ST

INNER JOIN @Message MSG

ON ST.StoreId = MSG.StoreId

)

SELECT

BD.StoreId

,MAX(CASE WHEN BD.ST_RID = 1 THEN BD.[Messages] END ) AS M1

,MAX(CASE WHEN BD.ST_RID = 2 THEN BD.[Messages] END ) AS M2

,MAX(CASE WHEN BD.ST_RID = 3 THEN BD.[Messages] END ) AS M3

,MAX(CASE WHEN BD.ST_RID = 4 THEN BD.[Messages] END ) AS M4

,MAX(CASE WHEN BD.ST_RID = 5 THEN BD.[Messages] END ) AS M5

FROM BASE_DATA BD

GROUP BY BD.StoreId;

Results

StoreId M1 M2 M3 M4 M5

----------- -------- -------- -------- -------- --------

100 Message1 Message2 Message3 Message4 Message5

200 Message1 Message2 Message3 Message4 Message5

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Jayanth_Kurup (9/3/2015)
pivot makes sense if the message column is a int or numerical data type on which aggregations need to be performs else if the number of messages is static you can use xml to pivot the data for you.

Quick thought, although it's straight forward to transpose the data using XML, I would be very careful suggesting such a solution when there are many unknowns such as cardinality and row size. It can work fine on small sets but it will never perform anywhere near the cross-tab.

😎

born2achieve Hall of Fame Points: 3182 More actions · Answer 4

Hi Eirik is there performance difference between pivot and cross tab? Curious to know. Thanks

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 5

Thanks Eirik for the straightforward method. Appreciated.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

born2achieve (9/3/2015)
Hi Eirik is there performance difference between pivot and cross tab? Curious to know. Thanks

In simple terms there is hardly any difference on smaller sets (10K rows or less), when the sets become larger then the cross-tab is normally faster although it's not always that simple. Suggest you have a look at Jeff Moden's article on the matter: Cross Tabs and Pivots[/url]

😎

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

KGJ-Dev (9/3/2015)
Thanks Eirik for the straightforward method. Appreciated.

You are welcome.

😎

Quick question, will you always know the number of columns in the output?

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 8

Yes, i know the number columns in the output always.

thanks