CODEWITHSUNDEEP
sql-serversql-server-2008duplicatesrow
Haggan
Haggan

Reputation: 79

Duplicate Rows in SQL Server 2008

I have below view with one Column, "PositionDate". I want to add a new column, "Currency" with Values "SEK", "EUR", "DKK" for every row. Result should be 3 rows / positiondate with one Currency per row.

Current:

PositionDate
2017-04-11
2017-04-10
2017-04-09

Desired:

PositionDate    Currency
2017-04-11      SEK
2017-04-11      DKK
2017-04-11      EUR
2017-04-10      SEK
2017-04-10      DKK
2017-04-10      EUR

Thanks for first answers! Now a new question. Say I have below table. How do I pick up the latest previous value in case my joined table to not match the date and currency? On the null DKK value I want it to pick up 3. Note that dates do not exist every day since I do not load the tables on weekends. 

Select
    PositionDate,
    Currency,
    T2.Value,
    isnull(t2.value, ? )
From t1
left join t2
on t1.currency = t2.Currency
and t1.PositionDate = t2.PositionDate

.

PositionDate    Currency        Value
2017-04-11      SEK               1
2017-04-11      DKK               NULL
2017-04-11      EUR               7
2017-04-10      SEK               4 
2017-04-10      DKK               3
2017-04-10      EUR               5
2017-04-07      SEK               4 
2017-04-07      DKK               3
2017-04-07      EUR               5

.

Upvotes: 0

Views: 49

Answers (3)

cloudsafe
cloudsafe

Reputation: 2506

With all values hard-coded: 

    select p.PositionDate, c.Currency 
    from (values('2017-04-11'), ('2017-04-10'), ('2017-04-09')) as P([PositionDate])
    cross join (values('skk'), ('dkk'), ('ekk')) c(Currency)

Upvotes: 0

GarethD
GarethD

Reputation: 69819

You can cross join with a table value constructor to do this:

SELECT  v.PositionDate, c.Currency
FROM    dbo.YourView AS v
CROSS JOIN (VALUES ('SEK'), ('EUR'), ('DKK')) AS c (Currency);

Example on DBFiddle

If you have your currencies stored in a table, then you can use this table instead of a table value constructor:

SELECT  v.PositionDate, c.Currency
FROM    dbo.YourView AS v
CROSS JOIN dbo.Currency AS c;

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28938

Use Cross apply

;with dte(a)
as
(
select '2017-04-11' union all
select '2017-04-10' union all
select '2017-04-09')
select * from dte c 
cross apply
(
 values('skk'),
       ('dkk'),
       ('ekk')
) b(f)

Upvotes: 1

Related Questions