CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-servicesssrs-2008
Infinity
Infinity

Reputation: 898

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

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.

Upvotes: 1

Views: 422

Answers (3)

P.Salmon
P.Salmon

Reputation: 17615

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.

Upvotes: 2

Aditya
Aditya

Reputation: 2301

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

Upvotes: 0

gofr1
gofr1

Reputation: 15977

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

Upvotes: 0

Related Questions