April 14, 2014 at 2:01 pm
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
April 14, 2014 at 2:12 pm
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/
April 15, 2014 at 3:37 am
please send me sample result 🙂
April 15, 2014 at 5:57 am
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
April 15, 2014 at 5:58 am
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
April 15, 2014 at 7:24 am
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.
April 15, 2014 at 4:28 pm
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;
April 17, 2014 at 12:46 am
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