Creating rows from Columns ....

• A Narro

  SSC Enthusiast

  Points: 137

  More actions

  July 22, 2005 at 2:20 pm

  #88306

  I need to create an SP where I am being passed a SQL query and need to convert the data so that some of the columns remain as columns and some of the columns become rows in the returned record set.

  The passed SQL will look something like: SELECT  A1.Geography_ID, AL1.Region_Nm, AL1.State_Nm, AL3.Data_Yr,  AL2.SalesRate, AL2.TotSales, AL2.Items FROM TIME_PERIOD_DIM AL3, GEOGRAPHY_DIM AL1 LEFT OUTER JOIN SALES_FACT AL2 ON (AL1.Geography_ID=AL2.Geography_ID) WHERE (AL3.Time_Period_ID=AL2.Time_Period_ID)  AND (AL3.Time_Period='2002' AND AL1.Geography_Typ=1)

  What I need is the resulting table to have Geography_ID, Region_Nm, State_Nm, and Data_Yr, and new colums Column_ Name and Value.

  If the query returns:

  Geography_ID	Region_Nm	State_Nm	Data_Yr	SalesRate	TotSales	Items
  1	East	VA	2002	5.9	45	3
  2	West	WA	2002	7.1	33	2

  I would like:

  Geography_ID	Region_Nm	State_Nm	Data_Yr	Column_Name	Value
  1	East	VA	2002	SalesRate	5.9
  1	East	VA	2002	TotSales	45
  1	East	VA	2002	Items	3
  2	West	WA	2002	SalesRate	7.1
  2	West	WA	2002	TotSales	33
  2	West	WA	2002	Items	2

  Now I will be getting queries with unknown columns, I will be given the names of the colums to keep (the green above) but they may not always be the first columns.

   

  Thanks

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  July 22, 2005 at 3:26 pm

  #576580

  I'm not sure how to solve the dynamic nature of what your discussing, but a query to return as your example requests

  SELECT  A1.Geography_ID, AL1.Region_Nm, AL1.State_Nm, AL3.Data_Yr,  AL2.SalesRate

  FROM TIME_PERIOD_DIM AL3, GEOGRAPHY_DIM AL1

  LEFT OUTER JOIN SALES_FACT AL2 ON (AL1.Geography_ID=AL2.Geography_ID)

  WHERE (AL3.Time_Period_ID=AL2.Time_Period_ID) 

  AND (AL3.Time_Period='2002' AND AL1.Geography_Typ=1)

  union

  SELECT  A1.Geography_ID, AL1.Region_Nm, AL1.State_Nm, AL3.Data_Yr,  AL2.TotSales,

  FROM TIME_PERIOD_DIM AL3, GEOGRAPHY_DIM AL1

  LEFT OUTER JOIN SALES_FACT AL2 ON (AL1.Geography_ID=AL2.Geography_ID)

  WHERE (AL3.Time_Period_ID=AL2.Time_Period_ID) 

  AND (AL3.Time_Period='2002' AND AL1.Geography_Typ=1)

  Union

  SELECT  A1.Geography_ID, AL1.Region_Nm, AL1.State_Nm, AL3.Data_Yr,  AL2.Items

  FROM TIME_PERIOD_DIM AL3, GEOGRAPHY_DIM AL1

  LEFT OUTER JOIN SALES_FACT AL2 ON (AL1.Geography_ID=AL2.Geography_ID)

  WHERE (AL3.Time_Period_ID=AL2.Time_Period_ID) 

  AND (AL3.Time_Period='2002' AND AL1.Geography_Typ=1)

  Maybe you can go from there

   

• A Narro

  SSC Enthusiast

  Points: 137

  More actions

  July 25, 2005 at 12:21 pm

  #576899

  Ray - Thanks, I am doing that now but the problem is that we will need this capability in our next data release.

  Thanks

• noeld

  SSC Guru

  Points: 96590

  More actions

  July 25, 2005 at 12:36 pm

  #576909

  for something that dynamic I'll create the query client side. Ah and change the "union" for "union all"

   

  
  * Noel