Sql colums and rows

• mmatseke

  Ten Centuries

  Points: 1129

  More actions

  August 26, 2004 at 7:11 am

  #108999

  I am getting this from my client

  TableA

  StorID LineNumber Text

  30000  1  ClientName

  30000 2  ClientSurname

  30000 3  3333333

  30000 4  20-05-2004

   

  I actually want this to look like

  TableB

  StoryID Name Surname Policy Date

  30000 Name Surname 3333333 20-05-2004

   

  Can you help with the script to convert this to one record with many fields (as in TableB)  if this is

• Gabor Nyul

  SSCrazy Eights

  Points: 9459

  More actions

  August 26, 2004 at 2:14 pm

  #520781

  What about this solution?:

  select T1.StorID,

   T1.Text1 as "Name",

   T2.Text1 as "ClientSurname",

   T3.Text1 as "Policy",

   T4.Text1 as "Date"

  from MyTableT1

   join MyTable T2 on T1.StorID = T2.StorID and T2.LineNumber = 2

   join MyTable T3 on T1.StorID = T3.StorID and T3.LineNumber = 3

   join MyTable T4 on T1.StorID = T4.StorID and T4.LineNumber = 4

  where T1.LineNumber = 1

  
  
  Bye
  Gabor

• Clayton Groom

  SSC-Addicted

  Points: 410

  More actions

  August 26, 2004 at 11:59 pm

  #520809

  The best approach to solving this is a "table Pivoting" technique that uses a CASE statment to drop row values into specific columns, and a group by to collapse the source rows down to a single row.

  The MAX() function returns the non-null result for each column in the group for the StorID, resulting in a single row.

  This technique can be used to do all kinds of tricky aggregations and data manipulations, including generating MTD and YTD totals in a single pass of a table. The technique almost always results in a table scan, as will the method outlined by the previous poster. The advantage to this technique is that there will only be one table scan instead of 4, and no collation operations for each of the self-joins on the table.

  ---

  select

  StorID

      ,max(Case when LineNumber = 1 then [Text] else null end ) as [Name]

      ,max(Case when LineNumber = 2 then [Text] else null end ) as [ClientSurname]

      ,max(Case when LineNumber = 3 then [Text] else null end ) as [Policy]

      ,max(Case when LineNumber = 4 then [Text] else null end ) as [Date]

  from

  TableA

  Group

  by StorID

   

  Kindest Regards,

  Clayton

• steved

  SSC Rookie

  Points: 40

  More actions

  August 27, 2004 at 8:21 pm

  #520968

  For all kinds of crosstabs/pivoting problems you can check out the RAC utility for S2k.RAC can handle both static and dynamic creation of pivot columns.RAC is easy to use,no complicated sql coding is necessary.RAC is a native server application that is highly integrated with S2k for advanced users.If your familar with Access crosstab you won't have any problem making the jump to RAC

  RAC v2.2 and QALite @

  http://www.rac4sql.net

   

   

  
  Check out RAC at:
  www.angelfire.com/ny4/rac/