views:

458

answers:

2

These are my rows that I initially retrieved:

112   Cem   Ceminay
210   Ali   Salih
132   Gül   Sen

Now I want to clone every row to be duplicated as 3 rows. So the new results:

112   Cem   Ceminay
112   Cem   Ceminay
112   Cem   Ceminay
210   Ali   Salih
210   Ali   Salih
210   Ali   Salih
132   Gül   Sen
132   Gül   Sen
132   Gül   Sen

What kind of select statement can help me?

Example select statement:

SELECT id,name,surname FROM people;

Thank you lovely people...

PS: My query:

            SELECT 
        Faturalar.faturaNo
        ,Klinikler.SAPSirketKodu [COMPANY CODE]
        ,Klinikler.SAPBussinessArea [BUSINESS AREA]
        ,BasilmisFaturalar.basilmisFatura_id [REFERENCE]
        ,BasilmisFaturalar.duzenlemeTarihi [DOCUMENT DATE]
        ,BasilmisFaturalar.duzenlemeTarihi [BASELINE DATE]
        ,Kurumlar.sapKodu [ACCOUNT]
        ,Kurumlar.kurumAdi + ' adına '+Faturalar.faturaNo+' nolu fatura' [TEXT]
        ,BasilmisFaturalar.kdvDahilToplamTutar [AMOUNT]
        ,BasilmisFaturalar.kdvHaricToplamTutar
        ,(BasilmisFaturalar.kdvDahilToplamTutar-BasilmisFaturalar.kdvHaricToplamTutar) AS kdvTutari
        ,CASE 
        WHEN BasilmisFaturalar.kdvOrani = 0 THEN 'A0'
        WHEN BasilmisFaturalar.kdvOrani = 8 THEN '2H'
        WHEN BasilmisFaturalar.kdvOrani = 18 THEN '3H'
        END [TAX CODE]
        ,Klinikler.profitCenter [COST CENTER/PROFIT CENTER]
        FROM Faturalar 
        -- Fatura yazdırılmış olmalı
        INNER JOIN BasilmisFaturalar ON BasilmisFaturalar.basilmisFatura_id = Faturalar.refBasilmisFatura_id
        -- Yazdırılmış fatura iptal edilmemiş olmalı
        INNER JOIN BasiliFaturaIptalTalepleri ON BasiliFaturaIptalTalepleri.refBasilmisFatura_id <> BasilmisFaturalar.basilmisFatura_id
        -- Fatura Kurum Detaylarından KURUM bilgilerine
        INNER JOIN KurumFaturaDetaylari ON KurumFaturaDetaylari.kurumFaturaDetay_id = BasilmisFaturalar.refKurumFaturaDetay_id
        -- Faturanın kesildiği kurum
        INNER JOIN Kurumlar ON Kurumlar.kurum_id = dbo.KurumFaturaDetaylari.refKurum_id
        -- Seanslar üzerinden Klinik bilgilerine böylece SAPcc, SAPba, ProfitCenter a ulaşıyoruz
        INNER JOIN Seanslar ON Seanslar.refFatura_id = Faturalar.fatura_id

        INNER JOIN dbo.Klinikler ON dbo.Klinikler.klinik_id = Seanslar.refKlinik_id
        -- Belirli bir düzenleme tarihi aralığında olmalı
        WHERE BasilmisFaturalar.duzenlemeTarihi BETWEEN DATEADD(m,-1,GETDATE()) AND GETDATE() 
        GROUP BY Faturalar.faturaNo,BasilmisFaturalar.basilmisFatura_id,kdvDahilToplamTutar
        ,kdvHaricToplamTutar
        ,BasilmisFaturalar.kdvOrani
        ,BasilmisFaturalar.duzenlemeTarihi
        ,Kurumlar.sapKodu
        ,Klinikler.SAPBussinessArea
        ,Klinikler.SAPSirketKodu
        ,Klinikler.profitCenter
        ,Kurumlar.kurumAdi
+6  A: 

How about:

SELECT id,name,surname FROM people
UNION ALL
SELECT id,name,surname FROM people
UNION ALL
SELECT id,name,surname FROM people

and possibly add a

ORDER BY id, name

to it if you want it ordered.

Marc

marc_s
Exactly what I was going to say. +1
Matthew Jones
I want to multiply result :) Isn't exist anyway can quickly multiply resultset.Because my sql statement 42 lines and contains 7 tables
uzay95
@uzay: can you use gbn's answer? that might work better in your scenario then.
marc_s
@uzay: something like this, selecting the same rows multiple times, isn't something that's done very often, so I guess that's why there's no real "out-of-the-box" support for it in SQL Server
marc_s
@marc_s: absoultly this is the first time i needed something like that. This will be reduce my code behind codes.
uzay95
+11  A: 

To help explain: Cartesian product/cross join background

SELECT
    people.id, people.name, people.surname
FROM
    people
    CROSS JOIN
    (SELECT 1 AS foo UNION ALL SELECT 2 UNION ALL SELECT 3) bar;

After comment elsewhere about 7 tables and 42 lines

SELECT
    Singles.*
FROM
    (
    SELECT 
    Faturalar.faturaNo
    ,Klinikler.SAPSirketKodu [COMPANY CODE]
    ,Klinikler.SAPBussinessArea [BUSINESS AREA]
    ,BasilmisFaturalar.basilmisFatura_id [REFERENCE]
    ,BasilmisFaturalar.duzenlemeTarihi [DOCUMENT DATE]
    ,BasilmisFaturalar.duzenlemeTarihi [BASELINE DATE]
    ,Kurumlar.sapKodu [ACCOUNT]
    ,Kurumlar.kurumAdi + ' adına '+Faturalar.faturaNo+' nolu fatura' [TEXT]
    ,BasilmisFaturalar.kdvDahilToplamTutar [AMOUNT]
    ,BasilmisFaturalar.kdvHaricToplamTutar
    ,(BasilmisFaturalar.kdvDahilToplamTutar-BasilmisFaturalar.kdvHaricToplamTutar) AS kdvTutari
    ,CASE 
    WHEN BasilmisFaturalar.kdvOrani = 0 THEN 'A0'
    WHEN BasilmisFaturalar.kdvOrani = 8 THEN '2H'
    WHEN BasilmisFaturalar.kdvOrani = 18 THEN '3H'
    END [TAX CODE]
    ,Klinikler.profitCenter [COST CENTER/PROFIT CENTER]
    FROM Faturalar 
    -- Fatura yazdırılmış olmalı
    INNER JOIN BasilmisFaturalar ON BasilmisFaturalar.basilmisFatura_id = Faturalar.refBasilmisFatura_id
    -- Yazdırılmış fatura iptal edilmemiş olmalı
    INNER JOIN BasiliFaturaIptalTalepleri ON BasiliFaturaIptalTalepleri.refBasilmisFatura_id <> BasilmisFaturalar.basilmisFatura_id
    -- Fatura Kurum Detaylarından KURUM bilgilerine
    INNER JOIN KurumFaturaDetaylari ON KurumFaturaDetaylari.kurumFaturaDetay_id = BasilmisFaturalar.refKurumFaturaDetay_id
    -- Faturanın kesildiği kurum
    INNER JOIN Kurumlar ON Kurumlar.kurum_id = dbo.KurumFaturaDetaylari.refKurum_id
    -- Seanslar üzerinden Klinik bilgilerine böylece SAPcc, SAPba, ProfitCenter a ulaşıyoruz
    INNER JOIN Seanslar ON Seanslar.refFatura_id = Faturalar.fatura_id

    INNER JOIN dbo.Klinikler ON dbo.Klinikler.klinik_id = Seanslar.refKlinik_id
    -- Belirli bir düzenleme tarihi aralığında olmalı
    WHERE BasilmisFaturalar.duzenlemeTarihi BETWEEN DATEADD(m,-1,GETDATE()) AND GETDATE() 
    GROUP BY Faturalar.faturaNo,BasilmisFaturalar.basilmisFatura_id,kdvDahilToplamTutar
    ,kdvHaricToplamTutar
    ,BasilmisFaturalar.kdvOrani
    ,BasilmisFaturalar.duzenlemeTarihi
    ,Kurumlar.sapKodu
    ,Klinikler.SAPBussinessArea
    ,Klinikler.SAPSirketKodu
    ,Klinikler.profitCenter
    ,Kurumlar.kurumAdi
    ) Singles
    CROSS JOIN
    (SELECT 1 AS foo UNION ALL SELECT 2 UNION ALL SELECT 3) Multiplier;
gbn
+1 for not having to duplicate the query
Adam Robinson
Interesting approach - it might not duplicate the query, but it's not easily and quite obvious at first what this does, in my opinion.
marc_s
@marc: I disagree...seems pretty obvious to me.
Adam Robinson
@marc_s, true. Slightly more opaque but perhaps slightly more correct, for us anyway. Personally, I'd cross join against my handy numbers table though :-)
gbn
as I said - very interesting approach, I'll have to remember that - not sure if it's quite obvious at first sight to a SQL beginner, though (Adam and you are obviously both quite advanced, but will a SQL beginner "grok" this right away? I have my doubts).....
marc_s
@marc: Probably not, but that's why it's good to give a solution like that in this context. The output and goal is clearly defined, and the query is short. It shouldn't be too tough to pick it up :)
Adam Robinson
I'm not exactly a SQL beginner, and it took me a second to figure out why this was working. I think a beginner would have some trouble...though if there were a comment along with the statement explaining what it did, it would be fine, and a very slick way to accomplish it.
Beska
I think it would be upvoted more if he explained what the query was doing. To that end: It is using a table valued subquery with 3 rows which is named bar. It then does a cross join which is the same as an inner join with 1=1 as the criteria. It produces what is called a cartesian product but which is just one row from the first table for each row in the second table. Since there are 3 rows in the second table, bar, it produces 3 copies of the row from the first table, people. To adjust the number of copies, just adjust the number of rows in the second table, bar.
Will Rickards
@Will Rickards: Cartesian products are part of the basics for SQL language. Even your explanation mentions it. We have to assume someone knows what this is... and they know the outer/inner/cross join basics too. Anyone who joins in the where clause is using a cartesian product (as implicit cross join) too.
gbn
So sexy answer :D Thank you VERYYY much..
uzay95
You're welcome. Please can you accept it?
gbn
alwayssssssssss.... Thanks again.
uzay95