Need to un-pivot some columns, pivot one column

  • CREATE TABLE [dbo].[MyTable](

    [CampusID] [int] NOT NULL,

    [Campus] [varchar](20) NOT NULL,

    [TermID] [int] NOT NULL,

    [Term] [varchar](12) NOT NULL,

    [StudentID] [int] NOT NULL,

    [Qualification] [varchar](15) NOT NULL,

    [Programme] [varchar](5) NOT NULL,

    [Repeat1stYear] [int] NULL,

    [Repeat2ndYear] [int] NULL,

    [Repeat3rdYear] [int] NULL,

    [ProgTo2ndYear] [int] NULL,

    [ProgTo3rdYear] [int] NULL,

    [ProgToCompleteQual] [int] NULL,

    [NotReturn2ndYr] [int] NULL,

    [NotReturn3rdYr] [int] NULL,

    [NotReturn4thYr] [int] NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (12, N'Campus1', 3, N'2012', 159472, N'HBPP112', N'HBPP1', 0, 0, 0, 0, 0, 0, 0, 0, 0)

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (12, N'Campus1', 5, N'2013', 159472, N'HBPP112', N'HBPP1', 1, 0, 0, 1, 0, 0, 0, 0, 0)

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (12, N'Campus1', 6, N'2014', 159472, N'HBP112', N'HBP1', 0, 0, 0, 0, 0, 0, 0, 0, 0)

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (15, N'Campus2', 3, N'2012', 168167, N'BACC311', N'BACC1', 0, 0, 0, 1, 0, 0, 0, 0, 0)

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (15, N'Campus2', 3, N'2012', 177821, N'DSDM314', N'DSDM1', 0, 0, 0, 1, 0, 0, 0, 0, 0)

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (15, N'Campus2', 5, N'2013', 168167, N'BACC311', N'BACC2', 0, 0, 0, 0, 1, 0, 0, 0, 0)

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (15, N'Campus2', 5, N'2013', 177821, N'DSDM314', N'DSDM2', 0, 0, 0, 0, 1, 0, 0, 0, 0)

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (15, N'Campus2', 6, N'2014', 168167, N'BACC311', N'BACC3', 0, 0, 0, 0, 0, 1, 0, 0, 0)

    INSERT [dbo].[MyTable] ([CampusID], [Campus], [TermID], [Term], [StudentID], [Qualification], [Programme], [Repeat1stYear], [Repeat2ndYear], [Repeat3rdYear], [ProgTo2ndYear], [ProgTo3rdYear], [ProgToCompleteQual], [NotReturn2ndYr], [NotReturn3rdYr], [NotReturn4thYr])

    VALUES

    (15, N'Campus2', 6, N'2014', 177821, N'DSDM314', N'DSDM3', 0, 0, 0, 0, 0, 1, 0, 0, 0)

    SELECT

    [CampusID]

    ,[Campus]

    ,[TermID]

    ,[Term]

    ,[StudentID]

    ,[Qualification]

    ,[Programme]

    ,[Repeat1stYear]

    ,[Repeat2ndYear]

    ,[Repeat3rdYear]

    ,[ProgTo2ndYear]

    ,[ProgTo3rdYear]

    ,[ProgToCompleteQual]

    ,[NotReturn2ndYr]

    ,[NotReturn3rdYr]

    ,[NotReturn4thYr]

    FROM [dbo].[MyTable]

    ORDER BY studentid, termid

    -----------------------------------------------------------

    The following columns I need to unpivot:

    [Repeat1stYear]

    [Repeat2ndYear]

    [Repeat3rdYear]

    [ProgTo2ndYear]

    [ProgTo3rdYear]

    [ProgToCompleteQual]

    [NotReturn2ndYr]

    [NotReturn3rdYr]

    [NotReturn4thYr]

    -----------------------------------------------------------

    The following column I need to pivot:

    [Campus]

    -----------------------------------------------------------

    Result should look like:

    SELECT

    [CampusID]

    ,[Campus1]

    ,[Campus2]

    ,[TermID]

    ,[Term]

    ,[StudentID]

    ,[Qualification]

    ,[Programme]

    -- Under column Status (new column), the following entries should appear:

    ,[Repeat1stYear]

    ,[Repeat2ndYear]

    ,[Repeat3rdYear]

    ,[ProgTo2ndYear]

    ,[ProgTo3rdYear]

    ,[ProgToCompleteQual]

    ,[NotReturn2ndYr]

    ,[NotReturn3rdYr]

    ,[NotReturn4thYr]

    FROM [dbo].[MyTable]

    ORDER BY studentid, termid

  • It is entirely unclear what you expect for output. What should the output look like (actual values) for the sample data you posted?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • please send me sample result 🙂

  • Please test the following .

    select

    [CampusID]

    ,[Campus]

    ,[TermID]

    ,[Term]

    ,[StudentID]

    ,[Qualification]

    ,[Programme]

    ,[status]

    ,[value]

    from

    (

    SELECT

    [CampusID]

    ,[Campus]

    ,[TermID]

    ,[Term]

    ,[StudentID]

    ,[Qualification]

    ,[Programme]

    ,[Repeat1stYear]

    ,[Repeat2ndYear]

    ,[Repeat3rdYear]

    ,[ProgTo2ndYear]

    ,[ProgTo3rdYear]

    ,[ProgToCompleteQual]

    ,[NotReturn2ndYr]

    ,[NotReturn3rdYr]

    ,[NotReturn4thYr]

    FROM [dbo].[MyTable]

    )p

    unpivot (value for [status]in

    (

    [Repeat1stYear]

    ,[Repeat2ndYear]

    ,[Repeat3rdYear]

    ,[ProgTo2ndYear]

    ,[ProgTo3rdYear]

    ,[ProgToCompleteQual]

    ,[NotReturn2ndYr]

    ,[NotReturn3rdYr]

    ,[NotReturn4thYr]

    ))as unpvt

    ORDER BY studentid, termid

    Regards

    J. Siva Kumar

  • SELECT [CampusID],Campus1,Campus2,[TermID],[Term],[StudentID],[Qualification],[Programme],status FROM

    (

    SELECT [CampusID],Campus,[TermID],[Term],[StudentID],[Qualification],[Programme],

    IIF(Repeat1stYEar = '1' , 'Repeat1stYEar',

    IIF([Repeat2ndYear] = '1' , 'Repeat2ndYear',

    IIF(Repeat3rdYEar = '1' , 'Repeat3rdYEar',

    IIF(NotReturn2ndYr = '1' , 'NotReturn2ndYr',

    IIF(NotReturn3rdYr = '1' , 'NotReturn3rdYr',

    IIF(NotReturn4thYr = '1' , 'NotReturn4thYr',NULL)))))) status

    FROM [MyTable]

    )E

    PIVOT

    (

    MAX (Campus)

    FOR Campus IN

    ( Campus1,Campus2 )) AS pvt

    Regards,
    Mitesh OSwal
    +918698619998

  • SELECT t.CampusID,t.Campus,t.TermID,t.Term,t.StudentID,t.Qualification,t.Programme,s.Status

    FROM [dbo].[MyTable] t

    CROSS APPLY (

    VALUES

    ('Repeat1stYear',t.Repeat1stYear)

    ,('Repeat2ndYear',t.Repeat2ndYear)

    ,('Repeat3rdYear',t.Repeat3rdYear)

    ,('ProgTo2ndYear',t.ProgTo2ndYear)

    ,('ProgTo3rdYear',t.ProgTo3rdYear)

    ,('ProgToCompleteQual',t.ProgToCompleteQual)

    ,('NotReturn2ndYr',t.NotReturn2ndYr)

    ,('NotReturn3rdYr',t.NotReturn3rdYr)

    ,('NotReturn4thYr',t.NotReturn4thYr)

    ) s (Status,StatusValue)

    WHERE s.StatusValue = 1

    Far away is close at hand in the images of elsewhere.
    Anon.

  • sivaj2k , Mitesh Oswal , David Burrows - thank you for your replies, will test them later today and reply to this post with my findings.

    -----------------------------------------

    Below is a much simpler example, which illustrates what I require and also illustrates how to achieve the result.

    Sample data:

    OrderID = 1,2,3

    Manager = 'Rafael','Vladimir','Ulri'

    Worker = 'Elena','Julia','Jenny'

    SELECT 1, 'Rafael', 'Elena' UNION ALL

    SELECT 2, 'Vladimir', 'Julia' UNION ALL

    SELECT 3, 'Ulri', 'Jenny'

    -----------------------------------------

    Basic select statement to list data:

    SELECT

    [OrderID]

    ,[Manager]

    ,[Worker]

    FROM [TEST].[dbo].[Unpivoting]

    -----------------------------------------

    How to unpivot the data:

    SELECT OrderID, Employee = Manager,

    CASE WHEN [Manager] IS NOT NULL THEN 'Manager' END AS Statuses

    FROM [dbo].[Unpivoting]

    UNION ALL

    SELECT OrderID, Employee = Worker,

    CASE WHEN [Worker] IS NOT NULL THEN 'Worker' END AS Statuses

    FROM [dbo].[Unpivoting]

    ORDER BY OrderID;

  • SELECT [CampusID],Campus1,Campus2,[TermID],[Term],[StudentID],[Qualification],[Programme],status,

    [Value] FROM(

    select

    [CampusID]

    ,[Campus]

    ,[TermID]

    ,[Term]

    ,[StudentID]

    ,[Qualification]

    ,[Programme]

    ,[status]

    ,[value]

    from

    (

    SELECT

    [CampusID]

    ,[Campus]

    ,[TermID]

    ,[Term]

    ,[StudentID]

    ,[Qualification]

    ,[Programme]

    ,[Repeat1stYear]

    ,[Repeat2ndYear]

    ,[Repeat3rdYear]

    ,[ProgTo2ndYear]

    ,[ProgTo3rdYear]

    ,[ProgToCompleteQual]

    ,[NotReturn2ndYr]

    ,[NotReturn3rdYr]

    ,[NotReturn4thYr]

    FROM [dbo].[MyTable]

    )p

    unpivot (value for [status]in

    (

    [Repeat1stYear]

    ,[Repeat2ndYear]

    ,[Repeat3rdYear]

    ,[ProgTo2ndYear]

    ,[ProgTo3rdYear]

    ,[ProgToCompleteQual]

    ,[NotReturn2ndYr]

    ,[NotReturn3rdYr]

    ,[NotReturn4thYr]

    ))as unpvt) p

    PIVOT

    (

    MAX (Campus)

    FOR Campus IN

    ( Campus1,Campus2 )) AS pvt

    ORDER BY studentid, termid

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply