Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with SQL Query Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2013 1:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:40 PM
Points: 248, Visits: 569
Consider i have the below table structure

create table #temp(id int,entity nvarchar(50))
insert into #temp values(1,'Candy')
insert into #temp values(2,'Chocolate')

create table #temp2(name nvarchar(50),id int,value int)
insert into #temp2 values('Johnson',1,50)
insert into #temp2 values('Johnson',2,70)
insert into #temp2 values('Darick',1,30)
insert into #temp2 values('Darick',2,90)


I want the output as

name id candy chocolate
Johnson 1 50 70
Darick 1 30 90

I tried using pivot but could not acheive what i want. And coding @ afternoon is

Post #1519521
Posted Wednesday, December 4, 2013 2:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 11:29 PM
Points: 147, Visits: 587
seems to be complex, but you can try this..
SELECT pv.NAME , min(ID) id, Candy , Chocolate from (
SELECT Name , Candy , Chocolate from
(SELECT
Name, [entity] , value
FROM #temp a INNER JOIN #temp2 b on a.id = b.id
) x
pivot
(
sum (value)
FOR [entity] IN (Candy , Chocolate)
) p
) as pv Left Join #temp2 a on a.name = pv.name
group by pv.NAME , Candy , Chocolate





Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com
Post #1519534
Posted Wednesday, December 4, 2013 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 7,123, Visits: 13,496
Probably the best tool for this is a dynamic cross-tab. Jeff Moden has an excellent article here. If after reading the article you are still having difficulty, post back.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1519604
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse