Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Help with SQL Query Expand / Collapse
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: Monday, January 18, 2016 4:11 AM
Points: 298, Visits: 790
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


Group: General Forum Members
Last Login: Friday, January 8, 2016 12:13 AM
Points: 147, Visits: 600
seems to be complex, but you can try this..
SELECT pv.NAME , min(ID) id, Candy , Chocolate from (
SELECT Name , Candy , Chocolate from
Name, [entity] , value
FROM #temp a INNER JOIN #temp2 b on =
) x
sum (value)
FOR [entity] IN (Candy , Chocolate)
) p
) as pv Left Join #temp2 a on =
group by pv.NAME , Candy , Chocolate

Praveen D'sa
MCITP - Database Administrator 2008
Post #1519534
Posted Wednesday, December 4, 2013 6:43 AM



Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 7,887, Visits: 16,789
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