SQL to Split 1 Record into 2

Hi Guys,

Here is an example:
ID_NUMBER---STREET_DATE---NORMAL_PRICE---BASE_PRICE
001------------01/01/2006-------10.00--------------8.00

The output should be somewhat like this:
ID_NUMBER---STREET_DATE---CONDITION---RATE
001------------01/01/2006-------SRP--------10.00
001------------01/01/2006-------BSE--------8.00

The 3rd column CONDITION is contanst based on the Normal and Base Price.

I would appreciate any help.
Thanks.
asimahmed073100Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Or, to avoid two passes thru the main table:


SELECT id_number, street_date,
    type_of_price,
    CASE WHEN type_of_price = 'SRP' THEN normal_price ELSE base_price END
FROM tableName
CROSS JOIN (
    SELECT 'SRP' AS type_of_price
    UNION ALL
    SELECT 'BSE'
) AS types_of_price
ORDER BY id_number, condition DESC
0
 
adwisemanCommented:
Select ID_NUMBER, STREET_DATE, 'SRP' AS Condition, Normal_Price as Rate
FROM MyTable
UNION ALL
Select ID_NUMBER, STREET_DATE, 'SRP' AS Condition, Base_Price as Rate
FROM MyTable
order by ID_NUMBER, Condition
0
 
adwisemanCommented:
sorry, second recordset needed to be BSE

Select ID_NUMBER, STREET_DATE, 'SRP' AS Condition, Normal_Price as Rate
FROM MyTable
UNION ALL
Select ID_NUMBER, STREET_DATE, 'BSE' AS Condition, Base_Price as Rate
FROM MyTable
order by ID_NUMBER, Condition
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.