CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
user1624185
user1624185

Reputation: 89

Multi Column to One Column

I am writing a trigger and I am using inserted Inserted is a single row like 

ColumnX |ColumnY|ColumnZ|
=========================
 A      | B     |C      |

I want to insert results to another table like this

ColumnName |Value|
==================
ColumnX    | A   |
ColumnY    | B   |
ColumnZ    | C   |

I have a lot of tables how can I make this?

Upvotes: 1

Views: 89

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

UNPIVOT the result set, then insert them:

INSERT INTO Anothertable(Columnname, value)
SELECT Columnname, value
FROM tablename t
UNPIVOT
(
   value
   FOR ColumnName IN(ColumnX, ColumnY, ColumnZ)
) AS u;

SQL Fiddle Demo

To do this dymaincally:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(Columnname)
                      FROM table1 t
UNPIVOT
(
   value
   FOR ColumnName IN(ColumnX, ColumnY, ColumnZ)
) AS u
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SET @query = 'SELECT Columnname, value
              FROM table1 t
              UNPIVOT
              (
                 value
                 FOR ColumnName IN( ' + @cols + ' )
              ) AS u;'

execute(@query);

Updated SQL Fiddle Demo

Upvotes: 2

Darren
Darren

Reputation: 70728

CREATE TRIGGER TriggerName
ON YourDatabase.TableName
AFTER INSERT 
AS
   BEGIN
        INSERT INTO OtherTable (ColumnX, ColumnY, ColumnZ) 
        SELECT ColumnX, ColumnY, ColumnZ
        FROM inserted
  END
GO

Upvotes: 0

Related Questions