PIVOT 1

[French79]

I have data the I need to put into a table. However, it's all in one row.

Example is a snapshot, there are more columns in the table than listed below.

ID Male Female Gender Undefined White Black Asian RaceUn
1 1 0 0 0 1 0 0

I need to look at each row and only return the fields that have a 1 (True) in the following fashion:

ID Demographic
1 Male
1 Black

and so forth.

I assume that I would have to use a PIVOT function achieve this but I'm not comfortable with the code for PIVOT. If anyone could assist me with this, I would greatly appreciate it.

Here is a DDL to create the table and some data to be inserted into the table.


USE [MGS_Staging]
GO

/****** Object: Table [dbo].[ADHOC_G_DEMOGRAPHICS] Script Date: 12/28/2010 14:46:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DEMOGRAPHICS](

[DemoID] [int] NOT NULL,
[MALE] [int] NULL,
[FEMALE] [int] NULL,
[GENDER_UNDEFINED] [int] NULL,
[HISPANIC] [int] NULL,
[NOT_HISPANIC] [int] NULL,
[ETHNICITY_UNDEFINED] [int] NULL,
[ASIAN] [int] NULL,
[BLACK] [int] NULL,
[HAWAIIAN] [int] NULL,
[NATIVE_AMERICAN] [int] NULL,
[WHITE] [int] NULL,
[RACE_MULTIPLE] [int] NULL,
[RACE_UNDEFINED] [int] NULL,
[DISABLED] [int] NULL,
[NOT_DISABLED] [int] NULL,
[DISABILITY_UNDEFINED] [int] NULL,
[TARGET_DISABILITY] [int] NULL,
[NOT_TARGET_DISABILITY] [int] NULL,
[TARGET_DISABILITY_UNDEFINED] [int] NULL,
[BLIND] [int] NULL,
[DEAF] [int] NULL,
[CONVULSIVE_DISORDER] [int] NULL,
[MENTAL_ILLNESS] [int] NULL,
[MENTAL_RETARDATION] [int] NULL,
[PARTIAL_PARALYSIS] [int] NULL,
[TOTAL_PARALYSIS] [int] NULL,
[MISSING_LIMBS] [int] NULL,
[DISTORTION_OF_LIMBS] [int] NULL,
[DISABILITY_DETAIL_MULTIPLE] [int] NULL,
[DISABILITY_DETAIL_UNDEFINED] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO




insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (962,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (963,1,0,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (964,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (965,0,1,0,0,0,1,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (966,1,0,0,1,0,0,0,0,0,0,0,0,1,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (967,1,0,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (968,1,0,0,1,0,0,0,0,0,0,0,0,1,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (969,1,0,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (970,1,0,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (971,0,1,0,0,1,0,1,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (972,1,0,0,0,1,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (973,1,0,0,0,1,0,1,0,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (974,1,0,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (976,1,0,0,1,0,0,0,0,0,0,0,0,1,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (977,1,0,0,1,0,0,0,0,0,0,0,0,1,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (978,1,0,0,0,1,0,0,0,0,0,0,0,1,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (979,1,0,0,0,1,0,0,0,0,0,1,0,0,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (980,1,0,0,1,0,0,0,0,0,0,0,0,1,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (981,1,0,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)
insert into Demographics (DemoID,MALE,FEMALE,GENDER_UNDEFINED,HISPANIC,NOT_HISPANIC,ETHNICITY_UNDEFINED,ASIAN,BLACK,HAWAIIAN,NATIVE_AMERICAN,WHITE,RACE_MULTIPLE,RACE_UNDEFINED,DISABLED,NOT_DISABLED,DISABILITY_UNDEFINED,TARGET_DISABILITY,NOT_TARGET_DISABILITY,TARGET_DISABILITY_UNDEFINED,BLIND,DEAF,CONVULSIVE_DISORDER,MENTAL_ILLNESS,MENTAL_RETARDATION,PARTIAL_PARALYSIS,TOTAL_PARALYSIS,MISSING_LIMBS,DISTORTION_OF_LIMBS,DISABILITY_DETAIL_MULTIPLE,DISABILITY_DETAIL_UNDEFINED) values (982,1,0,0,0,0,1,0,0,0,0,1,0,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,1)

 

[markros]

You need to use UNPIVOT operator, like this

select DemoID, Demographic, Value from DEMOGRAPHICS D
UNPIVOT ([Value] for Demographic IN (
    [MALE],
    [FEMALE],
    [GENDER_UNDEFINED],
    [HISPANIC],
    [NOT_HISPANIC],
    [ETHNICITY_UNDEFINED],
    [ASIAN],
    [BLACK],
    [HAWAIIAN],
    [NATIVE_AMERICAN],
    [WHITE],
    [RACE_MULTIPLE],
    [RACE_UNDEFINED],
    [DISABLED],
    [NOT_DISABLED],
    [DISABILITY_UNDEFINED],
    [TARGET_DISABILITY],
    [NOT_TARGET_DISABILITY],
    [TARGET_DISABILITY_UNDEFINED],
    [BLIND],
    [DEAF],
    [CONVULSIVE_DISORDER],
    [MENTAL_ILLNESS],
    [MENTAL_RETARDATION],
    [PARTIAL_PARALYSIS],
    [TOTAL_PARALYSIS],
    [MISSING_LIMBS],
    [DISTORTION_OF_LIMBS],
    [DISABILITY_DETAIL_MULTIPLE],
    [DISABILITY_DETAIL_UNDEFINED])) unpvt

PluralSight Learning Library

 

[French79]

Thanks markros

 

[French79]

Let me ask one other thing about this, is there a way to make this dynamic in the sense of, not having to "decalre" each column name in the list that you stated. Basically is there a way to make this dynamic so it would do the same with either a smaller or larger number of columns.

Just wondering.

Again thanks.

Happy New Year to everyone that reads this post.

 

[markros]

Yes, it's easy enough to make it dynamic. You can get column names from Information_Schema.Columns and construct the same query dynamically.

Let me know if you need the exact code for this.

PluralSight Learning Library

 

[French79]

If you could that would be great...

 

[markros]

declare @Cols nvarchar(max), @SQL nvarchar(max)

set @Cols = STUFF((select ', ' + quotename(Column_Name) from Information_Schema.Columns
where Table_Name = 'Demographics' and COLUMN_NAME <> 'DemoID'
order by ORDINAL_POSITION 
for XML path('')),1,2,'')

set @SQL = 'select * from Demographics UNPIVOT ([Value] FOR Demographic IN (' + @Cols +  ')) unpvt'

execute(@SQL)

We use INFORMATION_SCHEMA.Columns to get columns name into a variable and then construct our dynamic UNPIVOT.

PluralSight Learning Library