CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
SuicideSheep
SuicideSheep

Reputation: 5550

Complex dynamic column horizontally?

I have the first table as below

enter image description here

And Second table as below

enter image description here

And I'm trying to display in such a way at SSRS as below

enter image description here

Basically using first table to fix the number of rows and using second table to dynamically load the columns and fit the data accordingly based on the ID.

I've no idea on how to construct the design of SQL result in such a way to be able to be displayed as demonstrated

Is there any sample on the net which will lead me to the right way on this matter?

Upvotes: 0

Views: 68

Answers (1)

Ilyes
Ilyes

Reputation: 14928

Try this:

DECLARE @First TABLE (ID INT, Description NVARCHAR(50), Amount INT);
DECLARE @Second TABLE (ID INT, Description NVARCHAR(50), Amount INT);

INSERT INTO @First VALUES
(1, N'Cash', 50),
(2, N'CC', 20);


INSERT INTO @Second VALUES
(1, N'C1', 10),
(2, N'C2', 20),
(1, N'C3', 40);

SELECT ID, FD AS Description, FA AS Amount, C1, C2, C3
FROM
    (
        SELECT S.ID, S.Description SD, F.Description FD, F.Amount FA, S.Amount SA
        FROM @First F FULL JOIN @Second S ON F.ID = S.ID
    ) P1
    PIVOT
    (
        MAX(SA) FOR SD IN ([C1], [C2], [C3])
    ) P2

Result:

+----+-------------+--------+------+------+------+
| ID | Description | Amount |  C1  |  C2  |  C3  |
+----+-------------+--------+------+------+------+
|  1 | Cash        |     50 | 10   | NULL | 40   |
|  2 | CC          |     20 | NULL | 20   | NULL |
+----+-------------+--------+------+------+------+

Demo

Update: Dynamic pivot

CREATE TABLE First (ID INT, Description NVARCHAR(50), Amount INT);
CREATE TABLE Second (ID INT, Description NVARCHAR(50), Amount INT);
DECLARE @Cols NVARCHAR(MAX), @Query NVARCHAR(MAX);

INSERT INTO First VALUES
(1, N'Cash', 50),
(2, N'CC', 20);


INSERT INTO Second VALUES
(1, N'C1', 10),
(2, N'C2', 20),
(1, N'C3', 40);

SET @Cols = STUFF((SELECT distinct ',' + QUOTENAME(S.Description) 
            FROM Second S
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');
SET @Query = N'SELECT ID, FD AS Description, FA AS Amount, '+ @cols + '
FROM
    (
        SELECT S.ID, S.Description SD, F.Description FD, F.Amount FA, S.Amount SA
        FROM First F FULL JOIN Second S ON F.ID = S.ID
    ) P1
    PIVOT
    (
        MAX(SA) FOR SD IN ('+@Cols+')
    ) P2'
EXECUTE (@Query)

Dynamic

Upvotes: 1

Related Questions