query help --possibly dynamic?

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  April 7, 2016 at 8:54 pm

  #309775

  Here's the scenario

  CREATE TABLE #Test

  (

  ColumnName varchar(100),

  Category VARCHAR(10),

  Key1 varchar(100)

  )

  INSERT INTO #Test

  SELECT 'ProdGroup' as ColumnaName, 'WEB' as Category, 100 as Key1

  UNION

  SELECT 'ProdGroup' AS ColumnaName, 'WEB' AS Category, 101 AS Key1

  UNION

  SELECT 'ProdGroup' as ColumnaName, 'WEB' as Category, 122 as Key1

  UNION

  SELECT 'CustomerGroup' as ColumnaName, 'CUST' as Category, 11212121 as Key1

  UNION

  SELECT 'CustomerGroup' as ColumnaName, 'CUST' as Category, 23232121 as Key1

  UNION

  SELECT 'CustomerGroup' as ColumnaName, 'CUST' as Category, 3232121 as Key1

  UNION

  SELECT 'CustomerGroup' as ColumnaName, 'CUST' as Category, 8232121 as Key1

  UNION

  SELECT 'ItemCode' as ColumnaName, 'ItemC' as Category, 3432121 as Key1

  UNION

  SELECT 'ItemCode' as ColumnaName, 'ItemC' as Category, 3432121 as Key1

  SELECT * FROM #Test

  CREATE TABLE #ToInsert

  (

  CustomerGroup VARCHAR(100),

  ItemCode VARCHAR(100),

  ProdGroup VARCHAR(100)

  )

  Now, this is what I need to accomplish,

  IF columnname = 'CustomerGroup', I need TO TAKE Value FOR Key1 COLUMN AND put it under CustomerGroup IN #ToInsert table

  IF columnname = 'ItemCode', I need TO TAKE Value FOR Key1 COLUMN AND put it under ItemCode IN #ToInsert table

  IF columnname = 'ProdGroup', I need TO TAKE Value FOR Key1 COLUMN AND put it under ProdGroup IN #ToInsert table

  Any help would be appreciated.

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  April 7, 2016 at 11:23 pm

  #1870705

  You need a cross-tab query.

  _____________
  Code for TallyGenerator

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  April 8, 2016 at 4:57 am

  #1870746

  Can you give me some examples?

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 8, 2016 at 7:32 am

  #1870795

  SQL_Surfer (4/8/2016)

  ---

  Can you give me some examples?

  not sure if this is what you want....tis always good to detail expected results, saves time and confusion

  SELECT CASE WHEN ColumnaName = 'CustomerGroup' THEN key1 ELSE NULL END AS CustomerGroup,

  .........

  FROM #test;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day