Query help: Group by without aggregate & number of columns is unknown

Question

UPDATE: I have added some clarification, updated the image of roughly how the final report should appear in SSRS & fixed the error in my DDL.

I have a requirement to produce an SSRS report based on a dataset. I dont know if this level of flexibility is possible via SSRS but wanted to check with others in the SSRS community.

Id like to know the best way to do this. I have a dataset and how the final report should loo.

Basically, for each location there is a TrainingLot/Group & a number of positions. There can be 1 or more position in each lot, up to infinity, but if infinity is not possible in SSRS, lets put a limit of 4 on it for the moment.

Can anyone advise the best way to get to the result set shown in the image.Note ive formatted this nicely in excel but the location can be repeated on each row.

Appreciate any help provided.

create table #posts
(
     Location varchar(20)
    ,TrainingLot varchar(20)
    ,TrainingPost varchar(20)
)

insert into #posts(Location,TrainingLot,TrainingPost)
values
('Lisbon', 'A111', 'foreground manager')
,('Lisbon', 'A111', 'aft manager')
,('Lisbon', 'A111', 'foreground surveyor')
,('Lisbon', 'A111', 'gate keeper')
,('Lisbon', 'A112', 'foreground manager')
,('Lisbon', 'A112', 'aft manager')
,('Lisbon', 'A112', 'foreground surveyor')
,('Lisbon', 'A112', 'gate keeper')
,('Dublin', 'B7777', 'cleaner')
,('Dublin', 'B7777', 'cleaner supervisor')
,('Dublin', 'D8876', 'cleaner')
,('New York', 'V8877', 'foreground manager')
,('New York', 'V8877', 'aft manager')
,('New York', 'V8877', 'stock room manager')
,('New York', 'V8877', 'cleaner')
,('New York', 'V8878', 'director of inspections')
,('London', 'A119', 'foreground manager')
,('London', 'A119', 'aft manager')
,('London', 'A119', 'foreground surveyor')
,('London', 'A115', 'gate keeper')
,('London', 'A115', 'Repair person')

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Tab Alleman, Nov 03 '15 at 17:05
@TabAlleman The problem is column name is different for each location, how you solve that? — Juan Carlos Oropeza, Nov 03 '15 at 17:06
@JuanCarlosOropeza What makes you say that? I don't even see column names in the desired output. — Tab Alleman, Nov 03 '15 at 17:09
@TabAlleman I know didnt include the column, but should be something like `Location, TrainingLot, TrainingPost 1, TrainingPost 2, TrainingPost 3` — Juan Carlos Oropeza, Nov 03 '15 at 17:11
In that case, why do you say they will be different for every location? All locations will have the same columns, and the ones that have fewer than the max columns will have NULLs for the empty columns. — Tab Alleman, Nov 03 '15 at 17:13
@TabAlleman after reading lad answer I realize just have to create an aditional column to be the column header to use on the dynamic pivot. — Juan Carlos Oropeza, Nov 03 '15 at 17:18
Hi All, I have updated to remove the error in the DDL as pointed out, & i have redefined the requirements: - final report needs to be in SSRS, There are no numbers to aggregate & if infinite/large number of training posts is impossible then lets limit it to 4 for the moment — wilson_smyth, Nov 04 '15 at 11:57

score 2 · Answer 1 · answered Nov 03 '15 at 17:08

You can use:

WITH cte AS
(
SELECT *, 
 rn = ROW_NUMBER() OVER(PARTITION BY Location,TrainingLot ORDER BY (SELECT 1))
FROM #posts
)
SELECT 
  Location,
  TrainingLot,
  TrainingPost1 = MAX(CASE WHEN rn=1 THEN TrainingPost END),
  TrainingPost2 = MAX(CASE WHEN rn=2 THEN TrainingPost END),
  TrainingPost3 = MAX(CASE WHEN rn=3 THEN TrainingPost END),
  TrainingPost4 = MAX(CASE WHEN rn=4 THEN TrainingPost END)
  -- up to 50
FROM cte
GROUP BY 
 Location,
  TrainingLot
ORDER BY  Location,
  TrainingLot

LiveDemo

Note sample data you provided are a bit different with report you want. For instance:

 ('London', 'C6543', 'Storefront manager')

Query help: Group by without aggregate & number of columns is unknown

1 Answers1