How to pass SQL values from one to multiple columns in SSRS Report?

Question

So I have stored procedure which returns data in following:

GroupId   FieldName    Value         
1         Foo          28
1         Bar          2
1         FooBar       11
1         Bizz         22
1         UserName     John Smith
2         Foo          4
2         Bar          13
2         FooBar       27
2         Bizz         23
2         UserName     Peter Jones
3         Foo          5
3         Bar          4
3         FooBar       12
3         Bizz         18
3         UserName     Lisa Johnson
...........

As you see in sample data above, there is 3 groups (1, 2 & 3 (for real there are 10 groups)). Each group have the same field names in FieldName column (It's like header) and in Value column are stored values.

I need to create SSRS Report in following:

Foo          28              4                5
Bar          2               13               4
FooBar       11              27               12
Bizz         22              23               18
UserName     John Smith      Peter Jones      Lisa Johnson

As you see above, each group should be in different column and headers stored in left side (1st column).

In 1st column I've passed FieldName;

In 2nd column I've passed expression: =IIF(Fields!GroupId.Value = 1, Fields!Value.Value, "")

In 3rd column I've passed expression: =IIF(Fields!GroupId.Value = 2, Fields!Value.Value, "")

In 4th column I've passed expression: =IIF(Fields!GroupId.Value = 3, Fields!Value.Value, "")

But I achieve output like this:

Foo          28                   
Bar          2                    
FooBar       11                   
Bizz         22                   
UserName     John Smith           
Foo                        4          
Bar                        13           
FooBar                     27           
Bizz                       23           
UserName                   Peter Jones
Foo                                      5
Bar                                      4
FooBar                                   12
Bizz                                     18
UserName                                 Lisa Johnson

Have you any ideas, what's wrong? Should I try to do something with groups? I've tried but also unsuccessfully. If something unclear - ask me, I'll try to provide more details.

@gofr1 how correctly could I achieve It with `PIVOT` successfully? Imagine that data from stored procedure I store to temp table, how could I use `PIVOT` correctly? Also data is dynamic, It can be up to 20 groups. — Infinity, May 31 '16 at 06:30

score 2 · Answer 1 · answered May 31 '16 at 07:28

If the order if the field name is not important to you then you can do this simply in SSRS using a matrix with a rowgroup of fieldname and a column group of groupid. If you don't want the headers then hide them by changing the text box visibility option.

gofr1 · Answer 2 · 2016-05-31T06:49:18.973

You can achieve this output via pivoting in SQL Server.

At first I create temptable with your input data:

CREATE TABLE #temptable (
    GroupId int, 
    FieldName nvarchar(max), 
    [Value] nvarchar(max)
)
INSERT INTO #temptable VALUES       
(1,         'Foo',          '28'),
(1,         'Bar',          '2'),
(1,         'FooBar',       '11'),
(1,         'Bizz',         '22'),
(1,         'UserName',     'John Smith'),
(2,         'Foo',          '4'),
(2,         'Bar',          '13'),
(2,         'FooBar',       '27'),
(2,         'Bizz',         '23'),
(2,         'UserName',     'Peter Jones'),
(3,         'Foo',          '5'),
(3,         'Bar',          '4'),
(3,         'FooBar',       '12'),
(3,         'Bizz',         '18'),
(3,         'UserName',     'Lisa Johnson')

Then I use dynamic SQL because we dont know how many GroupID's there are:

DECLARE @columns nvarchar(max), @sql nvarchar(max)
--Here we create a string like '[1],[2],[3]' named by GroupID's 
--because if there are many groupid's - manually assign columns for pivot 
--will be a long process
SELECT @columns = STUFF((SELECT DISTINCT ','+QUOTENAME(GroupId) FROM #temptable FOR XML PATH('')),1,1,'')

--Create sql statement to execute
SELECT @sql = '
SELECT *
FROM (
SELECT *
FROM #temptable
) as p
PIVOT(
MAX([Value]) FOR GroupId IN ('+@columns+')
) as pvt'
--And execute!
EXEC(@sql)

Output:

FieldName   1           2           3
Bar         2           13          4
Bizz        22          23          18
Foo         28          4           5
FooBar      11          27          12
UserName    John Smith  Peter Jones Lisa Johnson

Thank you for an answer, this looks like working in SQL side, but after refreshing dataset fields not appears. Have you ideas how to solve It? — Infinity, May 31 '16 at 07:46
Here is a nice answer about this https://stackoverflow.com/questions/21914345/ssrs-dataset-field-refresh-not-updating-for-tablix please, try — gofr1, May 31 '16 at 07:52
I know about `Refresh Fields` button in Dataset's properties, but what I meant after clicking this button fields still not appears. — Infinity, May 31 '16 at 07:59
I think pivoting won't work fine in this way only if it will have static number of columns. I suggest you to use matrix in SSRS. Sorry for your time :( — gofr1, May 31 '16 at 08:23

score 0 · Answer 3 · answered May 31 '16 at 06:56

Use the below SQL logic & pivot the data at report level ...

;WITH CTE AS (       
SELECT 1 GroupId ,'Foo' FieldName,'28' Value
 UNION ALL       
SELECT 1         ,'Bar'          ,'2'
 UNION ALL       
SELECT 1         ,'FooBar'       ,'11'
 UNION ALL       
SELECT 1         ,'Bizz'         ,'22'
 UNION ALL       
SELECT 1         ,'UserName'     ,'John Smith'
 UNION ALL       
SELECT 2         ,'Foo'          ,'4'
 UNION ALL       
SELECT 2         ,'Bar'          ,'13'
 UNION ALL       
SELECT 2         ,'FooBar'       ,'27'
 UNION ALL       
SELECT 2         ,'Bizz'         ,'23'
 UNION ALL       
SELECT 2         ,'UserName'     ,'Peter Jones'
 UNION ALL       
SELECT 3         ,'Foo'          ,'5'
 UNION ALL       
SELECT 3         ,'Bar'          ,'4'
 UNION ALL       
SELECT 3         ,'FooBar'       ,'12'
 UNION ALL       
SELECT 3         ,'Bizz'         ,'18'
 UNION ALL       
SELECT 3         ,'UserName'     ,'Lisa Johnson'
)

SELECT FieldName, CTE.Value
FROM CTE 
CROSS APPLY ( SELECT CTE.Value )A
ORDER BY FieldName

How to pass SQL values from one to multiple columns in SSRS Report?

3 Answers3