Pivot with a twist

• kevin_nikolai

  SSCarpal Tunnel

  Points: 4343

  More actions

  December 19, 2014 at 2:21 pm

  #299584

  CREATE TABLE [dbo].[Mytable](

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

  [StudentUID] [int] NOT NULL,

  [Qual] [nvarchar](120) NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[Mytable] ([RegYear], [StudentUID], [Qual]) VALUES (N'2011', 8314, N'HBPP112')

  INSERT [dbo].[Mytable] ([RegYear], [StudentUID], [Qual]) VALUES (N'2012', 8314, N'BACS315')

  INSERT [dbo].[Mytable] ([RegYear], [StudentUID], [Qual]) VALUES (N'2013', 8314, N'BACS315')

  INSERT [dbo].[Mytable] ([RegYear], [StudentUID], [Qual]) VALUES (N'2014', 8314, N'BACS315')

  INSERT [dbo].[Mytable] ([RegYear], [StudentUID], [Qual]) VALUES (N'2014', 8314, N'BMNG311')

  INSERT [dbo].[Mytable] ([RegYear], [StudentUID], [Qual]) VALUES (N'2012', 241401, N'SPJMA018')

  INSERT [dbo].[Mytable] ([RegYear], [StudentUID], [Qual]) VALUES (N'2014', 241401, N'SBM018')

  INSERT [dbo].[Mytable] ([RegYear], [StudentUID], [Qual]) VALUES (N'2014', 241401, N'SOPMA018')

  GO

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

  -- Query 1

  SELECT DISTINCT StudentUID,Qual INTO #A FROM [dbo].[Mytable] WHERE RegYear < 2014

  SELECT DISTINCT

  C.StudentUID,C.Qual,

  (SELECT '; ' + C.Qual

  FROM #A C

  WHERE C.StudentUID IN (8314)

  FOR XML PATH('')) [QualCode]

  --ORDER BY C.Qual

  INTO #B

  FROM #A C

  WHERE C.StudentUID IN (8314)

  GROUP BY

  C.StudentUID,

  C.Qual

  ORDER BY 1

  SELECT DISTINCT StudentUID,SUBSTRING(QualCode,3,100) QualCode

  FROM #B

  WHERE StudentUID IN (8314)

  DROP TABLE #A

  DROP TABLE #B

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

  -- Query 2

  SELECT DISTINCT StudentUID,Qual INTO #A FROM [dbo].[Mytable] WHERE RegYear < 2014

  SELECT DISTINCT

  C.StudentUID,C.Qual,

  (SELECT '; ' + C.Qual

  FROM #A C

  WHERE C.StudentUID IN (241401)

  FOR XML PATH('')) [QualCode]

  --ORDER BY C.Qual

  INTO #B

  FROM #A C

  WHERE C.StudentUID IN (241401)

  GROUP BY

  C.StudentUID,

  C.Qual

  ORDER BY 1

  SELECT DISTINCT StudentUID,SUBSTRING(QualCode,3,100) QualCode

  FROM #B

  WHERE StudentUID IN (241401)

  DROP TABLE #A

  DROP TABLE #B

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

  -- Query 1 and 2 combined

  -- You will notice the Qual gets mixed up if multiple StudentUID is used.

  -- How do you fix this ?

  SELECT DISTINCT StudentUID,Qual INTO #A FROM [dbo].[Mytable] WHERE RegYear < 2014

  SELECT DISTINCT

  C.StudentUID,C.Qual,

  (SELECT '; ' + C.Qual

  FROM #A C

  WHERE C.StudentUID IN (8314,241401)

  FOR XML PATH('')) [QualCode]

  --ORDER BY C.Qual

  INTO #B

  FROM #A C

  WHERE C.StudentUID IN (8314,241401)

  GROUP BY

  C.StudentUID,

  C.Qual

  ORDER BY 1

  SELECT DISTINCT StudentUID,SUBSTRING(QualCode,3,100) QualCode

  FROM #B

  WHERE StudentUID IN (8314,241401)

  DROP TABLE #A

  DROP TABLE #B

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

• Daniel Matthee

  Mr or Mrs. 500

  Points: 579

  More actions

  December 24, 2014 at 12:32 am

  #1766299

  Hi

  So this was not so of a twist after all 🙂

  Here is the code that you needed. The only difference is in the where clause where you combine the 2 queries into one.

  Hope this is what you were looking for.

  CREATE TABLE [dbo].[Mytable]

  (

  [RegYear] [VARCHAR](12) NOT NULL ,

  [StudentUID] [INT] NOT NULL ,

  [Qual] [NVARCHAR](120) NULL

  )

  ON [PRIMARY]

  GO

  INSERT [dbo].[Mytable]

  ( [RegYear], [StudentUID], [Qual] )

  VALUES ( N'2011', 8314, N'HBPP112' )

  INSERT [dbo].[Mytable]

  ( [RegYear], [StudentUID], [Qual] )

  VALUES ( N'2012', 8314, N'BACS315' )

  INSERT [dbo].[Mytable]

  ( [RegYear], [StudentUID], [Qual] )

  VALUES ( N'2013', 8314, N'BACS315' )

  INSERT [dbo].[Mytable]

  ( [RegYear], [StudentUID], [Qual] )

  VALUES ( N'2014', 8314, N'BACS315' )

  INSERT [dbo].[Mytable]

  ( [RegYear], [StudentUID], [Qual] )

  VALUES ( N'2014', 8314, N'BMNG311' )

  INSERT [dbo].[Mytable]

  ( [RegYear], [StudentUID], [Qual] )

  VALUES ( N'2012', 241401, N'SPJMA018' )

  INSERT [dbo].[Mytable]

  ( [RegYear], [StudentUID], [Qual] )

  VALUES ( N'2014', 241401, N'SBM018' )

  INSERT [dbo].[Mytable]

  ( [RegYear], [StudentUID], [Qual] )

  VALUES ( N'2014', 241401, N'SOPMA018' )

  GO

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

  -- Query 1

  SELECT DISTINCT

  StudentUID ,

  Qual

  INTO #A

  FROM [dbo].[Mytable]

  WHERE RegYear < 2014

  SELECT DISTINCT

  C.StudentUID ,

  C.Qual ,

  ( SELECT '; ' + C.Qual

  FROM #A C

  WHERE C.StudentUID IN ( 8314 )

  FOR

  XML PATH('')

  ) [QualCode]

  --ORDER BY C.Qual

  INTO #B

  FROM #A C

  WHERE C.StudentUID IN ( 8314 )

  GROUP BY C.StudentUID ,

  C.Qual

  ORDER BY 1

  SELECT DISTINCT

  StudentUID ,

  SUBSTRING(QualCode, 3, 100) QualCode

  FROM #B

  WHERE StudentUID IN ( 8314 )

  DROP TABLE #A

  DROP TABLE #B

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

  -- Query 2

  SELECT DISTINCT

  StudentUID ,

  Qual

  INTO #A

  FROM [dbo].[Mytable]

  WHERE RegYear < 2014

  SELECT DISTINCT

  C.StudentUID ,

  C.Qual ,

  ( SELECT '; ' + C.Qual

  FROM #A C

  WHERE C.StudentUID IN ( 241401 )

  FOR

  XML PATH('')

  ) [QualCode]

  --ORDER BY C.Qual

  INTO #B

  FROM #A C

  WHERE C.StudentUID IN ( 241401 )

  GROUP BY C.StudentUID ,

  C.Qual

  ORDER BY 1

  SELECT DISTINCT

  StudentUID ,

  SUBSTRING(QualCode, 3, 100) QualCode

  FROM #B

  WHERE StudentUID IN ( 241401 )

  DROP TABLE #A

  DROP TABLE #B

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

  -- Query 1 and 2 combined

  -- You will notice the Qual gets mixed up if multiple StudentUID is used.

  -- How do you fix this ?

  SELECT DISTINCT

  StudentUID ,

  Qual

  INTO #A

  FROM [dbo].[Mytable]

  WHERE RegYear < 2014

  SELECT DISTINCT

  A.StudentUID ,

  A.Qual ,

  ( SELECT '; ' + C.Qual

  FROM #A C

  WHERE ( C.StudentUID = A.StudentUID )

  AND C.StudentUID IN ( 8314, 241401 )

  FOR

  XML PATH('')

  ) AS [QualCode]

  INTO #B

  FROM #A A

  WHERE A.StudentUID IN ( 8314, 241401 )

  GROUP BY A.StudentUID ,

  A.Qual

  ORDER BY 1

  SELECT DISTINCT

  StudentUID ,

  SUBSTRING(QualCode, 3, 100) QualCode

  FROM #B

  WHERE StudentUID IN ( 8314, 241401 )

  DROP TABLE #A

  DROP TABLE #B

  Kind Regards

  Daniel

  --------------------------------------------------------------------------------------------------------------------------------------------------------
  To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
  http://www.sql-sa.co.za

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  December 24, 2014 at 1:43 am

  #1766308

  If [Qual] is not needed in the result set, just exclude it .

  -- Query 1 and 2 combined

  SELECT DISTINCT

  StudentUID ,

  Qual

  INTO #A

  FROM [dbo].[Mytable]

  WHERE RegYear < 2014;

  SELECT A.StudentUID ,

  STUFF(( SELECT '; ' + C.Qual

  FROM #A C

  WHERE ( C.StudentUID = A.StudentUID )

  FOR

  XML PATH(''))

  ,1,2,'') AS [QualCode]

  INTO #B

  FROM #A A

  WHERE A.StudentUID IN ( 8314, 241401 )

  GROUP BY A.StudentUID

  ORDER BY 1;

  SELECT * FROM #B;

  DROP TABLE #A;

  DROP TABLE #B;