CODEWITHSUNDEEP
sqlsql-server
user3651247
user3651247

Reputation: 248

SQL Server - How to use a Column value which has already been unpivot

How to use a Column values that has already been unpivot into a new table. For example, we have a table that have a list of Column Names: [ID], [Name], [Country], [PostCode], [Address]. The Code I have (Note: Convert is for union purpose to have unique type) 

SELECT 
  ID,
  FieldName,
  FieldValue
FROM (SELECT
      CONVERT(VARCHAR(100), [ID]) AS [ID],
      CONVERT(VARCHAR(100), [Name]) AS [Name],
      CONVERT(VARCHAR(100), [Country]) AS [Country],
      CONVERT(VARCHAR(100), [PostCode]) AS [PostCode],
      CONVERT(VARCHAR(100), [Address]) AS [Address]
     FROM table) pv
UNPIVOT
(FieldValue FOR FieldName IN ([ID], [Name], [Country] 
[PostCode], [Address])) unp

But the above code will cause an issue that

Invalid column name 'ID'.

Is there any way to include the ID as a column name and all ID value as the list of values in the [ID] column?

For example, we have an original table 

ID    Name    Country    PostCode    Address
1     Jack    Australia  1234        Wyard
2     John    China      3223        Belconnen
3     Ed      UK         1123        Rose

I want to have the table will be look like below 

ID   FieldName   FieldValue 
1    ID          1
1    Name        Jack
1    Country     Australia
1    PostCode    1234
1    Address     Wyard
2    ID          2
2    Name        Leo
...    ...       ...

Upvotes: 0

Views: 68

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I would suggest using apply for this purpose:

SELECT t.ID, v.FieldName, v.FieldValue
FROM table t CROSS APPLY
     (VALUES (CONVERT(VARCHAR(100), [ID]), 'ID'),
             (CONVERT(VARCHAR(100), [Name]), 'Name'),
             (CONVERT(VARCHAR(100), [Country]), 'Country'),
             (CONVERT(VARCHAR(100), [PostCode]), 'PostCode'),
             (CONVERT(VARCHAR(100), [Address]), 'Address')
     ) v(FieldValue, FieldName);

Upvotes: 1

NoobX
NoobX

Reputation: 135

You can create another column using ID with some other alias in the nested query and don't refer it in the list of unpivoting columns.

SELECT 
  ID_C AS [ID],
  FieldName,
  FieldValue
FROM (SELECT
      CONVERT(VARCHAR(100), [ID]) AS [ID_C],
      CONVERT(VARCHAR(100), [ID]) AS [ID],
      CONVERT(VARCHAR(100), [Name]) AS [Name],
      CONVERT(VARCHAR(100), [Country]) AS [Country],
      CONVERT(VARCHAR(100), [PostCode]) AS [PostCode],
      CONVERT(VARCHAR(100), [Address]) AS [Address]
     FROM table) pv
UNPIVOT
(FieldValue FOR FieldName IN ([ID], [Name], [Country] 
[PostCode], [Address])) unp

This will require very less changes in your current query

Upvotes: 2

Related Questions