How can I pivot this query?

Question

5.00/5 (1 vote)

See more:

SQL-Server-2014

I've done some googling, but I am missing something and hoping the kind folk here can help. Simply, how can I pivot the results of this query, so that each Label is a column, and the corresponding Value a value in that Label's column?

select 
	  dets.Label
	, dets.Value
from
	(select
		  sc.SubmissionPkId
		, sn.Name
		, rs.Label
		, rs.Value
	from 
		CanvasResponse rs
		inner join CanvasScreen sn on rs.ScreenPkId = sn.PkId
		inner join CanvasSection sc on sn.SectionPkId = sc.PkId
	where 
		sn.Name = 'Seller Details' 
			or sn.Name = 'Property Details'			
		and Label in 
		(
			'OwnerName', 
			'OwnetEmail', 
			'OwnerContactNumber', 
			'Price', 
			'StreetNumber', 
			'StreetName', 
			'Suburb', 
			'Town'
		)
	) dets

Funny that at database level, it's more useful as is, but it is purely for presentation, and I'd much rather pivot on the server than in WPF code.

The data, pre-pivot, looks like this, give or take a few fields. I e.g. want OwnerName , OwnetEmail (sic) etc. as columns.

OwnerName:  Sipes v Vuuren
OwnetEmail:  	
OwnerContactNumber:  079654324
OwnerID:  	
Town:  Pretoria East (South)
Suburb:  Moreleta Park
StreetName:  Paul street
StreetNumber:  123

Posted 23-Feb-15 3:46am

Brady Kelly

Updated 23-Feb-15 4:02am

v2

Add a Solution

Comments

CHill60 23-Feb-15 9:52am

Have you got some sample data (just a few rows) and expected results?

Brady Kelly 23-Feb-15 10:02am

Added one example record.

CHill60 23-Feb-15 10:50am

Thanks for correcting the solution - cut&paste monster got me!

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 · 2015-02-23T04:36:00

Solution 1

Something like this (caveat - I haven't been able to test this properly so there might be the odd typo - used a temp table to mimic your query dets)

SQL

WITH dets as
(
select
          sc.SubmissionPkId
        , sn.Name
        , rs.Label
        , rs.Value
    from
        CanvasResponse rs
        inner join CanvasScreen sn on rs.ScreenPkId = sn.PkId
        inner join CanvasSection sc on sn.SectionPkId = sc.PkId
    where
        sn.Name = 'Seller Details'
            or sn.Name = 'Property Details'
        and Label in
        (
            'OwnerName',
            'OwnetEmail',
            'OwnerContactNumber',
            'Price',
            'StreetNumber',
            'StreetName',
            'Suburb',
            'Town'
        )

)
SELECT *
FROM (
    SELECT
        [SubmissionPkId], [Label], [Value]
    FROM dets

) as s
PIVOT
(
    MAX([Value])
    FOR [Label] IN (OwnerName, OwnetEmail, OwnerContactNumber, OwnerID, Town, Suburb, StreetName, StreetNumber )
)AS p

Give it a go and let me know where I got it wrong!

Posted 23-Feb-15 4:36am

CHill60

Updated 23-Feb-15 5:56am

v3

Comments

Brady Kelly 23-Feb-15 10:50am

It works very well, thank you, after the two small changes I made, but my big problem now is it only returns one row.

CHill60 23-Feb-15 10:52am

Try putting brackets around (sn.Name = 'Seller Details' or sn.Name = 'Property Details') - mixing 'and' and 'or' needs brackets to define the logic

Brady Kelly 23-Feb-15 11:08am

No diffs.

CHill60 23-Feb-15 11:30am

Hmm. I created a spoof table to represent some stuff in dets and got 2 rows back. Can you try actually creating the dets table temporarily, check it has several rows in it and then run the pivot against the temporary dets?

Maciej Los 23-Feb-15 10:53am

Sounds promising ;)

Brady Kelly 23-Feb-15 11:42am

If I run the 'dets' query on its own, I get 104 rows back.

CHill60 23-Feb-15 11:43am

Ok - so the problem is definitely in my pivot! :) I'm looking at it again

CHill60 23-Feb-15 11:47am

Got it. Where I had [Name] in my original solution try putting SubmissionPkId. I original had an [ID] field in my spoof query - when I take that out I also only get the one row.

Brady Kelly 23-Feb-15 11:55am

Wow! Thanks - I wish I could upvote and thank you more. That's exactly what I need.

CHill60 23-Feb-15 16:40pm

Phew! Glad we got there :)

CHill60 23-Feb-15 16:41pm

By the way - good one for updating the answer ... if anyone else hits a similar problem then they don't have to wade through loads of comments to find the final solution. Thank you!