CODEWITHSUNDEEP
sqlt-sqldynamic-sql
Ivan Nel
Ivan Nel

Reputation: 325

Dynamically Create SQL Columns using Table Rows

I have a extract that looks someting like this

| ID |   NAME   | ATTRIBUTE |  VALUE  |
|  1 |  PENCIL  | TYPE      |  HB2    |
|  1 |  PENCIL  | COLOR     |  RED    |
|  1 |  PENCIL  | MADE IN   | JAPAN   |
|  1 |  PENCIL  | HAS ERASER|  YES    |
|  2 |  LIGHT   | WATTS     |  60     |
|  2 |  LIGHT   | COLOR     |  WHITE  |
|  3 |  BOOK    | NAME      |  HELLO  |
|  3 |  BOOK    | WEIGHT    |  200G   |
|  3 |  BOOK    | ISBN      |  901551 |

I need assistance with a loop that would Count the max number of Attributes for an ID, in this example Pecil has 4 Attributes, and to then Create a table that has ID, Name, Attribute1, Value1, Attribute2, Value2, Attribute3, Value3, Attribute4, Value4.

If a product only has 2 Attributes, then Attributes and Values 3 and 4 would be blank.

| ID |   NAME   | ATTRIBUTE1 |  VALUE1  | ATTRIBUTE2 |  VALUE2  | ATTRIBUTE3 |  VALUE3  |
|  1 |  PENCIL  | TYPE       |  HB2     | COLOR      |  RED     |  MADE IN   |  JAPAN   | ...etc
|  2 |  LIGHT   | WATTS      |  60      | COLOR      |  WHITE   |            |          |
|  3 |  BOOK    | NAME       |  HELLO   | WEIGHT     |  200G    | ISBN       |  901551  |

I am still trying to dynamically add the columns.

DECLARE @RunningTotal BIGINT = 1;
DECLARE @MAXAttributeCnt BIGINT = (SELECT MAX(cnt) FROM (SELECT ID,count(1) as cnt FROM Table1 group by ID ) x);


WHILE @RunningTotal <= @MAXAttributeCnt

BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        SET @SQL = 'ALTER TABLE Table1
                    ADD ATTRIBUTE' + CAST(@RunningTotal as varchar(10)) + ' BIGINT NULL'

        
        EXECUTE (@SQL)
        SET @RunningTotal = @RunningTotal + 1    
END

But that is not working. I get a few loops of

> Could not find stored procedure 'ALTER TABLE Table1 ADD ATTRIBUTE1 BIGINT NULL'.

Once the Table is built, I would need a similar loop to populate the Fields. Any assistance would be appreciated.

UPDATE: I dont know if updating the OP is the accepted practice here. Please correct me if there is a different method.

Thank you for the assistance below, wrapping @SQL in () has worked. I now have a table with 4 Attribute and 4 Value Columns added, all dynamically named. However - I still need to populate it with the Attributes and Values. Anyone have an idea of how that would work ? This is what I now have, All nulls up to VALUE4.

| ID |   NAME   | ATTRIBUTE |  VALUE  | ATTRIBUTE1 | VALUE1 | ATTRIBUTE2 |...
|  1 |  PENCIL  | TYPE      |  HB2    |    NULL    |  NULL  | NULL       |...
|  1 |  PENCIL  | COLOR     |  RED    |    NULL    |  NULL  | NULL       |...
|  1 |  PENCIL  | MADE IN   | JAPAN   |    NULL    |  NULL  | NULL       |...
|  1 |  PENCIL  | HAS ERASER|  YES    |    NULL    |  NULL  | NULL       |...
|  2 |  LIGHT   | WATTS     |  60     |    NULL    |  NULL  | NULL       |...
|  2 |  LIGHT   | COLOR     |  WHITE  |    NULL    |  NULL  | NULL       |...
|  3 |  BOOK    | NAME      |  HELLO  |    NULL    |  NULL  | NULL       |...
|  3 |  BOOK    | WEIGHT    |  200G   |    NULL    |  NULL  | NULL       |...
|  3 |  BOOK    | ISBN      |  901551 |    NULL    |  NULL  | NULL       |...

Upvotes: 0

Views: 68

Answers (1)

sysopmatt
sysopmatt

Reputation: 26

Your error is because you're using EXEC @SQL, but EXEC executes a stored procedure, and you're just giving it a SQL command. You should use the stored procedure sp_executesql to run SQL commands like the following:

EXECUTE sp_executesql @SQL

Or as Charlieface has pointed out below, you can wrap @SQL in parenthesis like so:

EXECUTE (@SQL)

Upvotes: 1

Related Questions