0

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       |...
Ivan Nel
  • 319
  • 3
  • 12
  • `EXECUTE (@SQL)` – JamieD77 Feb 11 '21 at 22:33
  • How many columns do you need? 4? 10? 1000? Are you trying to add extra columns to `Table1` and denormalize it, why would you do that? – Charlieface Feb 11 '21 at 23:13
  • it will go up to the MAXAttributeCnt value. On my other system its running up to 19 Attributes and 19 Values = 38 Columns. – Ivan Nel Feb 12 '21 at 10:48
  • @Charlieface It needs to be displayed this way. – Ivan Nel Feb 12 '21 at 10:49
  • So why don't you just `SELECT` what you need, just wondering why do you need to actually change the base table? Pivoting using `CROSS APPLY` is pretty standard, there are hundreds of examples to google. If you are having trouble with actual pivoting, you should create a new question. Only update the OP if it relates to the same question. – Charlieface Feb 12 '21 at 11:00
  • @Charlieface I dont think PIVOT will work when the rows are mostly unique. Also, i want to name the columns Attribute + n+1 and not the value of the row. If it is possible to use PIVOT i will create a new question. thanks – Ivan Nel Feb 12 '21 at 11:16
  • 1
    Yeah you need to pivot on rownumber, and because you have multiple columns you need `APPLY` see https://stackoverflow.com/questions/947281/multiple-column-pivot-in-t-sql – Charlieface Feb 12 '21 at 11:22

1 Answers1

1

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)
sysopmatt
  • 26
  • 2