How can I combine different rows?

Hi!

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?

Re: How can I combine different rows?

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: How can I combine different rows?

>> I have a table looking like ..

Please post DDL instead of your personal narrative. If you had done
the talbe properly, i mgiht look like this:

CREATE TABLE AccountHistory
(acct_nbr INTEGER NOT NULL,
product_nbr INTEGER NOT NULL,
product_cnt INTEGER DEFAULT 1 NOT NULL
CHECK(product_cnt BETWEEEN ! AND 4),
PRIMARY KEY (acct_nbr, product_nbr, product_cnt),
user_name VARCHAR(25) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));

I left out the REFERENCES clause you would need and some other
things.

>> which I can have up to 4 lines [sic] for the same client. <<

Lines appear on a paper form or an input screen; a table has rows.
You need a constraint to enforce this rule.

>> I wish to transfer those lines into a new table looking like .. <<

You also failed to give any rules for sorting the repeating groups.
But th real question is why are you doing this at all?? That would
violate First Normal Form (1NF). This is not a good way to write
SQL.