Multiple Row groups in matrix

Question

Post reply

Multiple Row groups in matrix

sandy-1031461

SSC Enthusiast

Points: 172
More actions
May 10, 2009 at 4:42 am

#384552

Hi all,
Can anyone please help me in having more than one row group in matrix.
Available Data :
ITEMS Item Name Uom GrpId GrpIdVal
101001 Item-A kg pkz1 23
101001 Item-A kg pkz2 0.1
101001 Item-A kg arm1 0.2
909090 Item-B kg pkz2 10
909090 Item-B kg arm2 12
Output using matrix should be:
Items Item Name Uom pkz1 pkz2 arm1 arm2
101001 Item-A kg 23 0.1 0.2 0
909090 Item-B kg 0 10 0 12
The columns are dynamic can increase.
I tried this giving spaces and align accordingly to columns the preview looks good but when deployed , the column header of the row groups are concatenated as below
Items Item Name Uom pkz1 pkz2 arm1 arm2
101001 Item-A kg 23 0.1 0.2 0
909090 Item-B kg 0 10 0 12
Exporting to excel is another problem.
I pray to god for the person who gives me the solution.;-)

Viewing 3 posts - 1 through 2 (of 2 total)

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Not sure if this is what you meant

-- Create And Populate Test Table

CREATE TABLE dbo.TestTable (ITEMS int, [Item Name] varchar(20), Uom varchar(4), GrpId int, GrpIdVal decimal(9,1))

INSERT dbo.TestTable VALUES (101001, 'Item-A kg', 'pkz', 1, 23)

INSERT dbo.TestTable VALUES (101001, 'Item-A kg', 'pkz', 2, 0.1)

INSERT dbo.TestTable VALUES (101001, 'Item-A kg', 'arm', 1, 0.2)

INSERT dbo.TestTable VALUES (909090, 'Item-B kg', 'pkz', 2, 10)

INSERT dbo.TestTable VALUES (909090, 'Item-B kg', 'arm', 2, 12)

-- Actual Query

DECLARE @sql nvarchar(max)

SET @sql = N'SELECT ITEMS,[Item Name]'

SELECT @sql = @sql + ',SUM(CASE WHEN Uom+CAST(GrpId as varchar)='''+a.Uom+CAST(a.GrpId as varchar)+''' THEN GrpIdVal ELSE 0.0 END) AS ['+a.Uom+CAST(a.GrpId as varchar)+']'

FROM dbo.TestTable a GROUP BY a.Uom,a.GrpId

ORDER BY a.Uom

SET @sql = @sql + N'

FROM dbo.TestTable

GROUP BY ITEMS,[Item Name]

ORDER BY ITEMS,[Item Name]'

-- PRINT @sql -- For Debugging

EXEC sp_executesql @sql

Far away is close at hand in the images of elsewhere.
Anon.

sandy-1031461 SSC Enthusiast Points: 172 More actions · Answer 2

Sorry mate i was looking for SSRS matrix solution...the data i had given is a test data, als the columns are dynamic..

Thanks for the effort....:-)