How can I use the values Selected in a While Exists statement inside the While loop?

Question

I'm new-ish to SQL and am trying to figure out how to use the values from the Select statement in a While Exists conditional loop. The purpose is to combine multiple occurences of an attribute for a Document into a single field, and later pivot and join those results to the Document record.

For example, three tables exist like so:

ATTRIBUTES TABLE

ID, ATTRIBUTE_NAME
---------------------------
1, Created
2, Embedded_Image
...

ATTRIBUTE_VALUES TABLE

ATTRIBUTE_ID, VALUE, DOC_ID
-------------------------------------------
1, 2010/11/01, 1
2, 'Home.png', 1
2, 'Castle.png', 1
2, 'Apartment.jpg', 1
1, 2008/06/23, 2
2, 'Ski Jump.jpg', 2
2, 'Snowboarding.png', 2
...

DOCUMENTS TABLE

ID, TEXT
---------------------------
1, 'Homes of the ...'
2, 'Winter sports ...'
...

So a final Pivot and Join of the tables would look like so:

DOC_ID, TEXT, Created, Embedded_Image
----------------------------------------------------------------------------------------
1, 'Homes of the ...', 2010/11/01, 'Home.png,Castle.png,Apartment.jpg'
2, 'Winter sports ...', 2008/06/23, 'Ski Jump.jpg, Snowboarding.png'

The SQL While Exists condition I've tried to write looks like so:

    DECLARE @LOOP_DOC_ID UNIQUEIDENTIFIER
    DECLARE @LOOP_ATTRIBUTE_NAME NVARCHAR(MAX)

    WHILE EXISTS(
      SELECT [dbo].[ATTRIBUTE_VALUES].[DOC_ID], [dbo].[ATTRIBUTES].[ATTRIBUTE_NAME]
      FROM ([dbo].[ATTRIBUTE_VALUES] INNER JOIN [dbo].[ATTRIBUTES]
      ON [dbo].[ATTRIBUTE_VALUES].[ATTRIBUTE_ID] = [dbo].[ATTRIBUTES].[ID])
    )
    BEGIN
      SET @LOOP_DOC_ID = DOC_ID
      SET @LOOP_ATTRIBUTE_NAME = ATTRIBUTE_NAME

      SELECT STUFF(
        (
          SELECT DISTINCT ',' + RTRIM(LTRIM([dbo].[ATTRIBUTE_VALUES].[VALUE]))
          FROM 
            (
              [dbo].[ATTRIBUTE_VALUES] INNER JOIN [dbo].[ATTRIBUTES]
              ON [dbo].[ATTRIBUTE_VALUES].[ATTRIBUTE_ID] = [dbo].[ATTRIBUTES].[ID]
            )
          WHERE [dbo].[ATTRIBUTE_VALUES].[DOC_ID] = @LOOP_DOC_ID
                AND [dbo].[ATTRIBUTES].[ATTRIBUTE_NAME] = @LOOP_ATTRIBUTE_NAME
          ORDER BY ',' + RTRIM(LTRIM([dbo].[ATTRIBUTE_VALUES].[VALUE]))
          FOR XML PATH('')
        ), 1, 2, ''
      ) AS VALUE, @LOOP_DOC_ID AS DOC_ID, @LOOP_ATTRIBUTE_NAME AS ATTRIBUTE_NAME
    END
    

SQL Server doesn't like the lines where I'm trying to SET the variables to the values from the Select statement in the While Exists condition.

How can I use the [dbo].[ATTRIBUTE_VALUES].[DOC_ID], [dbo].[ATTRIBUTES].[ATTRIBUTE_NAME] values Selected in the While Exists conditional statement between the BEGIN and END statements?

Preferrably I would like to do away with the @LOOP_DOC_ID and @LOOP_ATTRIBUTE_NAME variables and deal directly with the values.

I've looked through forums that have talked about using Cursors to solve similar problems, but each one of them seem to recommend only using Cursors as a last resort due to their lack of speed. I've also seen some people use stored procedures, but I can't use those, since my boss has ruled those as off-limits. Am I in need of a Cursor, or is there a better way to do this?

Answer 1

Have a look at something like this (Full Example)

DECLARE @ATTRIBUTES TABLE(
        ID INT, 
        ATTRIBUTE_NAME VARCHAR(100)
)

INSERT INTO @ATTRIBUTES SELECT 1, 'Created'
INSERT INTO @ATTRIBUTES SELECT 2, 'Embedded_Image'

DECLARE @ATTRIBUTE_VALUES TABLE(
        ATTRIBUTE_ID INT, 
        VALUE VARCHAR(100), 
        DOC_ID INT
)
INSERT INTO @ATTRIBUTE_VALUES SELECT 1, '2010/11/01', 1
INSERT INTO @ATTRIBUTE_VALUES SELECT 2, 'Home.png', 1
INSERT INTO @ATTRIBUTE_VALUES SELECT 2, 'Castle.png', 1
INSERT INTO @ATTRIBUTE_VALUES SELECT 2, 'Apartment.jpg', 1
INSERT INTO @ATTRIBUTE_VALUES SELECT 1, '2008/06/23', 2
INSERT INTO @ATTRIBUTE_VALUES SELECT 2, 'Ski Jump.jpg', 2
INSERT INTO @ATTRIBUTE_VALUES SELECT 2, 'Snowboarding.png', 2

DECLARE @DOCUMENTS TABLE(
    ID INT, 
    [TEXT] VARCHAR(100)
)

INSERT INTO @DOCUMENTS SELECT 1, 'Homes of the ...'
INSERT INTO @DOCUMENTS SELECT 2, 'Winter sports ...'

;WITH Vals AS (
        SELECT  d.ID DOC_ID,
                d.[TEXT] [TEXT],
                a.ATTRIBUTE_NAME,
                av.VALUE
        FROM    @DOCUMENTS d INNER JOIN
                @ATTRIBUTE_VALUES av ON d.ID = av.DOC_ID INNER JOIN
                @ATTRIBUTES a   ON  av.ATTRIBUTE_ID = a.ID
)
SELECT  *
FROM    (
            SELECT  DOC_ID,
                    [TEXT],
                    ATTRIBUTE_NAME,
                    stuff(
                            (
                                select  ',' + t.VALUE
                                from    Vals t
                                where   t.DOC_ID = v.DOC_ID
                                AND     t.ATTRIBUTE_NAME = v.ATTRIBUTE_NAME
                                order by t.VALUE
                                for xml path('')
                            ),1,1,'') Concats
            FROM    Vals v
            GROUP BY    DOC_ID,
                        [TEXT],
                        ATTRIBUTE_NAME
        ) s
PIVOT   ( MAX(ConCats) FOR ATTRIBUTE_NAME IN ([Created],[Embedded_Image])) pvt

Output

DOC_ID  TEXT    Created Embedded_Image
1   Homes of the ...    2010/11/01  Apartment.jpg,Castle.png,Home.png
2   Winter sports ...   2008/06/23  Ski Jump.jpg,Snowboarding.png

Answer 2

From your sample, and with support from common sense, I venture the hypothesis that

1. A document has a single creation date.
2. A document can have many embedded images.

So pivoting on creation date is straightforward:

SELECT DOC_ID
, VALUE AS Created
FROM ATTRIBUTE_VALUES
WHERE ATTRIBUTE_ID = 1

and joining this subquery to your Documents table gives you the first three columns of your desired output.

Your final column summarizes multiple embedded images for each document. I personally would use some standard reporting tool (e.g. MS Access or Crystal Reports). Alternatively, create a new empty table with your four desired columns, populate the first three columns with a SQL INSERT statement, and then have Perl (or C#, or your favorite declarative language) query for the embedded images of each document, concatenate the results with commas, and insert the concatenation into your fourth column.

But if you want to do it in SQL, the concatenate-multiple-values question has been asked here before, e.g. in How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?.