CODEWITHSUNDEEP
sql-servert-sql
Anuj Tamrakar
Anuj Tamrakar

Reputation: 83

How do i pivot a table?

I have a dynamic single row Table like:

PersonId|FirstName|LastName|Address|PhoneNumber
-----------------------------------------------
1        Anuj      Tamrakar  NY     +525418

I want to pivot this table and want an output in temp table like:

PersonalDetails|Value
----------------------
PersonId        1
FirstName       Anuj
LastName        Tamrakar
Address         NY
PhoneNumber     +525418

The first Table is a dynamic single row temp table. For this example, I have 5 columns. I may have more or less columns depending on my criteria

Upvotes: 2

Views: 41

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You actually want to UNPIVOT:

SELECT  PersonalDetails, Value
FROM 
   (SELECT CAST([PersonId] AS VARCHAR(MAX)) AS [PersonId], 
           CAST([FirstName] AS VARCHAR(MAX))  AS [FirstName],
           CAST([LastName] AS VARCHAR(MAX)) AS [LastName],
           CAST([Address] AS VARCHAR(MAX)) AS [Address],
           CAST([PhoneNumber] AS VARCHAR(MAX)) AS [PhoneNumber]
   FROM mytable) p
UNPIVOT
   (Value FOR PersonalDetails IN 
      ([PersonId], [FirstName], [LastName], [Address], [PhoneNumber])
) AS unpvt;

All 'to-be-unpivoted' fields have to be of the same type, hence the use of CAST.

Demo here

For a dynamic number of columns you have to use dynamic sql:

DECLARE @cols VARCHAR(MAX) = ''
DECLARE @cast_cols VARCHAR(MAX) = ''
DECLARE @qry VARCHAR(MAX)

SELECT @cols = @cols + ',[' + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable' AND TABLE_SCHEMA='dbo'

SELECT @cast_cols = @cast_cols + ',CAST([' + COLUMN_NAME + '] AS VARCHAR(MAX)) AS [' + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable' AND TABLE_SCHEMA='dbo'

SET @cols = STUFF(@cols, 1, 1, '')
SET @cast_cols = STUFF(@cast_cols, 1, 1, '')    

SET @qry = 'SELECT  PersonalDetails, Value FROM (' 
           + @cast_cols +        
          'FROM mytable) p
           UNPIVOT
           (Value FOR PersonalDetails IN (' + @cols + ')
           ) AS unpvt'
EXEC (@qry)

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

If you really have a single row in the original table, then you can use a series of UNION operations to get your output:

SELECT 'PersonId'    AS PersonalDetails, PersonId AS    Value
FROM yourTable
UNION ALL
SELECT 'FirstName'   AS PersonalDetails, FirstName AS   Value
FROM yourTable
UNION ALL
SELECT 'LastName'    AS PersonalDetails, LastName AS    Value
FROM yourTable
UNION ALL
SELECT 'Address'     AS PersonalDetails, Address AS     Value
FROM yourTable
UNION ALL
SELECT 'PhoneNumber' AS PersonalDetails, PhoneNumber AS Value
FROM yourTable

Upvotes: 2

Simply Me
Simply Me

Reputation: 1587

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.yourTableName')

This gives you your column names. You simply insert the column names in your new table and use ``, insert into temp(PersonalDetails, Value) values(column1,select column1 from SingleRowTable

Upvotes: 0

Related Questions