Need to un-pivot some columns, pivot one column

Question

Need to un-pivot some columns, pivot one column

kevin_nikolai

SSCarpal Tunnel

Points: 4343
More actions
April 14, 2014 at 2:01 pm

#292440

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

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

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/

TEJABI Old Hand Points: 368 More actions · Answer 2

TEJABI

Old Hand

Points: 368

April 15, 2014 at 3:37 am

#1706023

please send me sample result 🙂

sivaj2k Ten Centuries Points: 1188 More actions · Answer 3

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

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 4

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

David Burrows SSC Guru Points: 65138 More actions · Answer 5

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.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 6

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;

TEJABI Old Hand Points: 368 More actions · Answer 7

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