Return a many-to-many relationship query to single line

Question

I was wondering if it is possible. I have 3 tables:

Item             ItemProperty               Property

ItemUID          ItemUID                    PropertyUID

Name             PropertyUID                Name

...              Value                      ....

But I want a query that returns everything in a single row, with the name of the property as the column name, like so

ItemUID        ItemName      Property1       Property2      Property2

109-2...       PostalCard    Value1          Value2         Value3

Is it possible? The reason I want it in this specific format and not a datatable is that we are limited with one of our tool.

I want to specify that the query would return only 1 row, not a datatable of multiple items since some items can contain only 1 property and others more than 5-10, there would be a "WHERE ITEMUID = @ItemUID" kind of.

SQLFiddle of the schema (I hope it works?) http://sqlfiddle.com/#!3/70f34/2

Answer 1

The static SQL you would need would be along the lines of:

SELECT  pvt.ItemUID,
        Property1 = pvt.[1],
        Property2 = pvt.[2],
        Property3 = pvt.[3]
FROM    (   SELECT  ItemUID,
                    PropertyUID,
                    RowNum = ROW_NUMBER() OVER(PARTITION BY ItemUID ORDER BY PropertyUID)
            FROM    ItemProperty
        ) AS ip
        PIVOT
        (   MAX(PropertyUID)
            FOR RowNum IN ([1], [2], [3])
        ) AS pvt;

This simply assigns each propertyUID a row number, then pivots based on this. the MAX(PropertyUID) is needed for the pivot function, but could just as easily be MIN since each combination of ItemUID and RowNum is unique there will only ever be one propertyUID to choose from.

If it is a varied number of properties then you will need to use dynamic SQL to do the pivoting, the end result will be similar, but you will need to use the maximum number of properties to create the SQL:

DECLARE @pvt NVARCHAR(MAX),
        @Cols NVARCHAR(MAX),
        @SQL NVARCHAR(MAX),
        @MaxProperties INT;

-- GET MAXIMUM NUMBER OF PROPERTIES FOR A SINGLE ITEM
SELECT  TOP 1 @MaxProperties = COUNT(*)
FROM    ItemProperty
GROUP BY ItemUID
ORDER BY COUNT(*) DESC;

-- CREATE A STRING LIKE '[1],[2],[3]...' TO USE INSIDE PIVOT
SET @pvt = STUFF((SELECT TOP (@MaxProperties) ',' + QUOTENAME(ROW_NUMBER() OVER(ORDER BY object_id))
                    FROM sys.all_objects
                    FOR XML PATH('')), 1, 1, '');


-- CREATE A STRING LIKE 'Property1 = pvt.[1],Property2 = pvt.[2]...' TO USE IN SELECT
SET @Cols = STUFF((SELECT TOP (@MaxProperties) ',Property' + CAST(ROW_NUMBER() OVER(ORDER BY object_id) AS VARCHAR(10)) 
                        + ' = pvt.' + QUOTENAME(ROW_NUMBER() OVER(ORDER BY object_id))
                    FROM sys.all_objects
                    FOR XML PATH('')), 1, 1, '');


-- BUILD SQL TO USE
SET @SQL = 'SELECT  pvt.ItemUID,' + @Cols + '
            FROM    (   SELECT  ItemUID,
                                PropertyUID,
                                RowNum = ROW_NUMBER() OVER(PARTITION BY ItemUID ORDER BY PropertyUID)
                        FROM    ItemProperty
                    ) AS ip
                    PIVOT
                    (   MAX(PropertyUID)
                        FOR RowNum IN (' + @pvt + ')
                    ) AS pvt;';

-- EXECUTE THE SQL THAT HAS BEEN DYNAMICALLY BUILT
EXECUTE sp_executesql @SQL;

Example on SQL Fiddle

N.B. I would usually reccommend using FOR XML PATH(''), TYPE and the XQuery method .value to create the SQL, but since it is just integers being concatenated I know there are no special characters so there is no need to worry about these not being escaped properly e.g.

SET @Cols = STUFF((SELECT TOP (10) ',Property' + CAST(ROW_NUMBER() OVER(ORDER BY object_id) AS VARCHAR(10)) 
                        + ' = pvt.' + QUOTENAME(ROW_NUMBER() OVER(ORDER BY object_id))
                    FROM sys.all_objects
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

---

EDIT

If you want to limit this to a single item then you just need to add a couple of WHERE clauses (to get the count to being with, and also when executing the dynamic sql):

DECLARE @ItemUID INT = 1;

DECLARE @pvt NVARCHAR(MAX),
        @Cols NVARCHAR(MAX),
        @SQL NVARCHAR(MAX),
        @MaxProperties INT;

-- GET MAXIMUM NUMBER OF PROPERTIES FOR A SINGLE ITEM
SELECT  @MaxProperties = COUNT(*)
FROM    ItemProperty
WHERE   ItemUID = @ItemUID
GROUP BY ItemUID
ORDER BY COUNT(*) DESC;

-- CREATE A STRING LIKE '[1],[2],[3]...' TO USE INSIDE PIVOT
SET @pvt = STUFF((SELECT TOP (@MaxProperties) ',' + QUOTENAME(ROW_NUMBER() OVER(ORDER BY object_id))
                    FROM sys.all_objects
                    FOR XML PATH('')), 1, 1, '');


-- CREATE A STRING LIKE 'Property1 = pvt.[1],Property2 = pvt.[2]...' TO USE IN SELECT
SET @Cols = STUFF((SELECT TOP (@MaxProperties) ',Property' + CAST(ROW_NUMBER() OVER(ORDER BY object_id) AS VARCHAR(10)) 
                        + ' = pvt.' + QUOTENAME(ROW_NUMBER() OVER(ORDER BY object_id))
                    FROM sys.all_objects
                    FOR XML PATH('')), 1, 1, '');


-- BUILD SQL TO USE
SET @SQL = 'SELECT  pvt.ItemUID,' + @Cols + '
            FROM    (   SELECT  ItemUID,
                                PropertyUID,
                                RowNum = ROW_NUMBER() OVER(PARTITION BY ItemUID ORDER BY PropertyUID)
                        FROM    ItemProperty
                        WHERE   ItemUID = @ItemUID
                    ) AS ip
                    PIVOT
                    (   MAX(PropertyUID)
                        FOR RowNum IN (' + @pvt + ')
                    ) AS pvt;';

-- EXECUTE THE SQL THAT HAS BEEN DYNAMICALLY BUILT
EXECUTE sp_executesql @SQL, N'@ItemUID INT', @ItemUID;

Answer 2

You will have to use Pivoting that way you can transform rows into coumns. see this example