Help with SQL Query

• Shadab Shah

  SSCarpal Tunnel

  Points: 4759

  More actions

  December 4, 2013 at 1:57 am

  #283228

  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

  nameid candy chocolate

  Johnson1 50 70

  Darick1 30 90

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

• PravB4u

  Default port

  Points: 1401

  More actions

  December 4, 2013 at 2:53 am

  #1670635

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 4, 2013 at 6:43 am

  #1670696

  Probably the best tool for this is a dynamic cross-tab. Jeff Moden has an excellent article here[/url]. 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