Pivot columns in ms SQL

Question

0.00/5 (No votes)

See more:

, +

I need to use pivot to create time table from the following data:
and records contain inner join.I have tried and provided code but not able to get it
I hope it is sufficient data

starttime endtime subjname dd
9            11    History Monday
11           13    Physics Tuesday

I'm using this query to generate above columns

SQL

select 
    start_time, end_time, s.subj_name, tt.dd
from 
    time_table tt 
inner join 
    subj s on tt.subjid = s.subjid

I am able to pivot my result using below code

SQL

--Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

--Get unique values of pivot column  
SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(dd)
FROM (SELECT DISTINCT(dd)
      FROM time_table tt
      WHERE divid = 1 AND tt.active = 1) AS PivotExample

SELECT @PivotColumns

--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N'SELECT start_time,end_time, ' +   @PivotColumns + ' 
    FROM [dbo].[time_table]  
    PIVOT( SUM(subjid) 
          FOR dd IN (' + @PivotColumns + ')) AS P' 

SELECT   @SQLQuery

--Execute dynamic query
EXEC sp_executesql @SQLQuery

And getting output as

Starttime endtime Monday Tuesday Wednesday
9          11     7        8       null
11         13     null     7         9

Here i am getting subjectid and but I want Subject Name which is in different as shown in above query,Help me out with this issue.I have tried to give max() agreegate function since it is an varchar but i guess getting error due to join
Thank you.

Posted 18-Jun-16 2:34am

Member 10202727

Updated 19-Jun-16 23:39pm

Maciej Los

v2

Add a Solution

Comments

RedDk 18-Jun-16 12:26pm

The word "PIVOT" is really a nasty database user's word for the more pleasant word, used by mathematicians (linear algebra) and the like, "TRANSPOSE". Look up transpose usg Google with "MS SQL" and "How to". The place to start ... experimenting.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-06-19T23:39:00

If you use the form of the PIVOT query that looks like

SQL

SELECT * FROM 
(
    -- source query
) src
PIVOT
(
    -- pivot definition
)

rather than the format you are using then you can easily add the join

--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N'SELECT * FROM
	(SELECT start_time,end_time,subj_name,dd
    FROM [dbo].[time_table] tt 
	INNER JOIN subj s on tt.subjid = s.subjid 
	) SRC
    PIVOT( MAX(subj_name) 
          FOR dd IN (' + @PivotColumns + ')) AS P'

Notice I'm using MAX() rather than SUM() to get the course name