|
|
Posted by Erland Sommarskog on 07/03/07 21:16
(hlajeunessse@gmail.com) writes:
> I have a table looking like
>
> (username) (account number) (start date) (end date) (product)
>
> wich I can have up to 4 lines for the same client.
>
> I wist to transfert those lines into a new table looking like
>
> (username) (account number) (start date 1) (end date 1) (product 1)
> (start date 2) (end date 2) ... (product 4)
>
> How (in SQL) I could do it?
SELECT username, accountnumber,
MIN(CASE rn WHEN 1 THEN startdate END),
MIN(CASE rn WHEN 1 THEN enddate END),
MIN(CASE rn WHEN 1 THEN product END),
MIN(CASE rn WHEN 2 THEN startdate END),
MIN(CASE rn WHEN 2 THEN enddate END),
MIN(CASE rn WHEN 2 THEN product END),
...
FROM (SELECT username, accountnumber, startdate, enddate, product,
rn = row_number()
OVER(PARTITION BY username, accountnumer
ORDER BY startdate, product) AS x
GROUP BY username, account
The trick is that by using MIN and GROUP by we get all on the same row,
else we would have four rows with NULL values in the column the row
does not apply to. Actually, it does not matter if you use MIN or MAX.
If you are on SQL 2000, you cannot use the row_number function to number
the rows. You can use:
rn = (SELECT COUNT(*)
FROM tbl b
WHERE a.username = b.username
AND a.accountnumber = b.accountnumber
AND (a.startdate < b.startdate OR
a.startdate = b.startdate AND a.product <= b.product)
But it will be very slow at large volumes.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|