1

This question already has an answer here:

I have two tables

land
===========
lid name meter address

owner
==========
ownerid  name family mobile lid

Every land can have maximum 12 owners.

The result that I need:

lid name meter address owner_name_1 owner_family_1 owner_mobile_1 owner_name_2 owner_family_2 owner_mobile_2 .... owner_name_12 owner_family_12 owner_mobile_12

I tried pivot but I can not write it correctly.

marked as duplicate by Andriy M, Erik, Paul White sql-server Sep 24 '16 at 3:23

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

One potential way of doing this would be with a common-table-expression combined with a ROW_NUMBER() aggregate to identify each owner as owner #1 to #12.

I solved it like this:

IF OBJECT_ID('tempdb..#land') IS NOT NULL
DROP TABLE #land;
CREATE TABLE #land
(
    lid INT NOT NULL IDENTITY(1,1)
    , name VARCHAR(30) NOT NULL
    , meter VARCHAR(30) NOT NULL
    , address VARCHAR(30) NOT NULL
);

IF OBJECT_ID('tempdb..#owner') IS NOT NULL
DROP TABLE #owner;
CREATE TABLE #owner
(
    ownerid INT NOT NULL IDENTITY(1,1)
    , name VARCHAR(30) NOT NULL
    , family VARCHAR(30) NOT NULL
    , mobile VARCHAR(30) NOT NULL
    , lid INT NOT NULL
);

INSERT INTO #land (name, meter, address)
VALUES ('port', 'meter1', 'address1')
    , ('star', 'meter2', 'address2');

INSERT INTO #owner (name, family, mobile, lid)
VALUES ('owner1', 'family1', 'mobile1', 1)
    , ('owner2', 'family2', 'mobile2', 1)
    , ('owner3', 'family3', 'mobile3', 1)
    , ('owner4', 'family4', 'mobile4', 1)
    , ('owner5', 'family5', 'mobile5', 1)
    , ('owner6', 'family6', 'mobile6', 1)
    , ('owner7', 'family7', 'mobile7', 1)
    , ('owner8', 'family8', 'mobile8', 2)
    , ('owner9', 'family9', 'mobile9', 2)
    , ('owner10', 'family10', 'mobile10', 2)
    , ('owner11', 'family11', 'mobile11', 2)
    , ('owner12', 'family12', 'mobile12', 2)
    , ('owner13', 'family13', 'mobile13', 2)
    , ('owner14', 'family14', 'mobile14', 2);


;WITH owners AS
(
        SELECT o.* 
            , rn = ROW_NUMBER() OVER (PARTITION BY o.lid ORDER BY o.ownerid)
        FROM #owner o
)
SELECT *
FROM #land l
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 1) t1(lid1, name1, family1, mobile1) ON t1.lid1 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 2) t2(lid2, name2, family2, mobile2) ON t2.lid2 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 3) t3(lid3, name3, family3, mobile3) ON t3.lid3 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 4) t4(lid4, name4, family4, mobile4) ON t4.lid4 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 5) t5(lid5, name5, family5, mobile5) ON t5.lid5 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 6) t6(lid6, name6, family6, mobile6) ON t6.lid6 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 7) t7(lid7, name7, family7, mobile7) ON t7.lid7 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 8) t8(lid8, name8, family8, mobile8) ON t8.lid8 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 9) t9(lid9, name9, family9, mobile9) ON t9.lid9 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 10) t10(lid10, name10, family10, mobile10) ON t10.lid10 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 11) t11(lid11, name11, family11, mobile11) ON t11.lid11 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 12) t12(lid12, name12, family12, mobile12) ON t12.lid12 = l.lid

It's not a particularly elegant or efficient way of solving this problem, but it seems to do what you want.

This is sample output (it's wider than this in reality, I just didn't want to include a super-wide image because it won't display well).

enter image description here

The select statement updated to not show all columns is:

;WITH owners AS
(
        SELECT o.* 
            , rn = ROW_NUMBER() OVER (PARTITION BY o.lid ORDER BY o.ownerid)
        FROM #owner o
)
SELECT l.lid
    , l.name
    , l.meter
    , l.address
    , name1, family1, mobile1
    , name2, family2, mobile2
    , name3, family3, mobile3
    , name4, family4, mobile4
    , name5, family5, mobile5
    , name6, family6, mobile6
    , name7, family7, mobile7
    , name8, family8, mobile8
    , name9, family9, mobile9
    , name10, family10, mobile10
    , name11, family11, mobile11
    , name12, family12, mobile12
FROM #land l
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 1) t1(lid1, name1, family1, mobile1) ON t1.lid1 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 2) t2(lid2, name2, family2, mobile2) ON t2.lid2 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 3) t3(lid3, name3, family3, mobile3) ON t3.lid3 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 4) t4(lid4, name4, family4, mobile4) ON t4.lid4 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 5) t5(lid5, name5, family5, mobile5) ON t5.lid5 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 6) t6(lid6, name6, family6, mobile6) ON t6.lid6 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 7) t7(lid7, name7, family7, mobile7) ON t7.lid7 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 8) t8(lid8, name8, family8, mobile8) ON t8.lid8 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 9) t9(lid9, name9, family9, mobile9) ON t9.lid9 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 10) t10(lid10, name10, family10, mobile10) ON t10.lid10 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 11) t11(lid11, name11, family11, mobile11) ON t11.lid11 = l.lid
    LEFT JOIN (SELECT owners.lid, owners.name, owners.family, owners.mobile FROM owners WHERE owners.rn = 12) t12(lid12, name12, family12, mobile12) ON t12.lid12 = l.lid;

Not the answer you're looking for? Browse other questions tagged or ask your own question.