Sql colums and rows
- 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 likeTableB
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 -
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 -
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
-
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 @
Check out RAC at:
www.angelfire.com/ny4/rac/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply