Problem with self join

• Shadab Shah

  SSCarpal Tunnel

  Points: 4759

  More actions

  January 13, 2014 at 12:16 am

  #288313

  Consider the below scripts

  CREATE TABLE #TEMP(Category nvarchar(100),Item nvarchar(100),Value nvarchar(100))

  INSERT INTO #TEMP values('Small','PilotA1','AjJCC')

  INSERT INTO #TEMP values('Large','PilotA1','ACC')

  INSERT INTO #TEMP values('Small','PilotA2','BKC')

  I want the result as

  PilotA1AjJCCACC

  PilotA2 BKC

  The closes i have come is

  SELECT t1.Item,t1.Value,t2.Value

  FROM #TEMP T1 (NOLOCK)

  JOIN #TEMP T2 (NOLOCK) on T1.Item=T2.Item

  and T1.Category = 'Small' and T2.Category='Large'

  But i am getting only one record.

  Could you please with this query.

• Koen Verbeeck

  SSC Guru

  Points: 259197

  More actions

  January 13, 2014 at 2:20 am

  #1679510

  CREATE TABLE #TEMP(Category NVARCHAR(100),Item NVARCHAR(100),Value NVARCHAR(100));

  INSERT INTO #TEMP values('Small','PilotA1','AjJCC');

  INSERT INTO #TEMP values('Large','PilotA1','ACC');

  INSERT INTO #TEMP values('Small','PilotA2','BKC');

  WITH CTE_T1 AS

  (

  SELECT Item, Value

  FROM #TEMP

  WHERE Category = 'SMALL'

  )

  ,CTE_T2 AS

  (

  SELECT Item, Value

  FROM #TEMP

  WHERE Category = 'LARGE'

  )

  SELECT t1.Item, t1.Value, ISNULL(t2.Value,'')

  FROMCTE_T1 t1

  FULL JOINCTE_T2 t2 ON t1.Item = t2.Item;

  -- full join as either category might be missing rows

  DROP TABLE #TEMP;

  Any special reason you use NOLOCK in your queries?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 13, 2014 at 8:48 am

  #1679613

  Is there any reason to use a JOIN when CROSS TABS will work fine?

  SELECT Item,

  MAX( CASE WHEN Category = 'Small' THEN Value END) ,

  MAX( CASE WHEN Category = 'Large' THEN Value END)

  FROM #TEMP

  GROUP BY Item

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• MysteryJimbo

  SSC-Insane

  Points: 24203

  More actions

  January 15, 2014 at 8:55 am

  #1680318

  Slightly different take on this by me.

  CREATE TABLE #TEMP(Category NVARCHAR(100),Item NVARCHAR(100),Value NVARCHAR(100));

  INSERT INTO #TEMP values('Small','PilotA1','AjJCC');

  INSERT INTO #TEMP values('Large','PilotA1','ACC');

  INSERT INTO #TEMP values('Small','PilotA2','BKC');

  SELECT *

  FROM

  (

  SELECT Item, Value, Category

  FROM #TEMP

  ) AS s

  PIVOT

  (

  MAX(Value) FOR Category IN (Small,Large)

  ) AS P