3

I have two tables one parent table 

ID Name
1  Sam
2  Ricky

Then i have a child table , here we have one to many relation ship like record sam has multiple address

Like (Child Table)

ID Parent_ID Address
1  1         Newyork
2  1         Chicago

Now what i wanna do is to write a query where i will get data from both the table like 

ID Name Address1 Address2
1  Sam  Newyork  Chicago

I know what is the maximum no of address present. Even some data has one address . in that case the Address2 will be null or empty.

Thanks for help !!

user3635201
  • 43
  • 4

3 Answers3

2

Using INNER JOIN and PIVOT u can get the result. Try this..

CREATE TABLE #par
  (
     ID   INT,
     Name VARCHAR(100)
  )

INSERT #par
VALUES (1,'Sam'),
       (2,'Ricky')

CREATE TABLE #chil
  (
     ID        INT,
     Parent_ID INT,
     Addresss  VARCHAR(100)
  )

INSERT #chil
VALUES( 1,1,'Newyork'),
       (2,1,'Chicago')


SET @col=(SELECT ',[' + Cast(Addresss AS VARCHAR(100)) + ']'
          FROM   #chil
          FOR XML PATH(''))

SELECT @col = RIGHT(@col, Len(@col) - 1)

SET @col1=(SELECT ',[' + Cast(Addresss AS VARCHAR(100))
                  + '] as Address'
                  + CONVERT(VARCHAR(50), Row_number() OVER (ORDER BY ID))
           FROM   #chil
           FOR XML PATH(''))

SELECT @col1 = RIGHT(@col1, Len(@col1) - 1)

SET @sql= 'SELECT id,name,' + @col1
          + '
    FROM   (SELECT a.id,
                   a.name,
                   b.Addresss Addresss
            FROM   #par a
                   INNER JOIN #chil b
                           ON a.id = b.Parent_ID) p
           PIVOT (Max(Addresss)
                 FOR Addresss IN(' + @col + ')) AS piv '

EXEC Sp_executesql
  @sql

OUTPUT 

id  name    Address1    Address2
1   Sam      Newyork    Chicago
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Declare the col,col1 and sql varibles with nvarchar – Pரதீப் Nov 05 '14 at 12:37
  • From the sound of it, the OP only cares about 2 addresses, so the dynamic query stuff shouldn't be necessary. Also, you might want to call out that `PIVOT` doesn't exist in MySQL. – Clockwork-Muse Nov 05 '14 at 13:38
  • @Clockwork-Muse without dynamic wuery how will you assign alias name as Address1 ,address2 based on the no. of address available in child table. – Pரதீப் Nov 05 '14 at 15:33
  • @Clockwork-Muse - then y sql server and sql server 2008 is tagged – Pரதீப் Nov 05 '14 at 15:35
  • MySQL (from Oracle) is a different implementation of the SQL Standard (from Microsoft), which is lacking in many features (like `PIVOT`, although that one's not very common yet). If he only wants a maximum of two (or some other low number) of addresses, I'd just permanently assign the column names, and not bother deriving them dynamically. Besides which, as the statement stands you'll get a count of **all** addresses, repeated at each person, which is incorrect (and likely far too long for the column list). – Clockwork-Muse Nov 05 '14 at 21:30
  • @NoDisplayName i am getting "Pivot columns must be comparable. The type of column "Addresss" is "text", which is not comparable." error kindly help. and thank you for your support. – user3635201 Dec 01 '14 at 07:32
-1

should be something like

select * from parent inner join child on child.parent_id = parent.id

but that is simple joining. maybe i missed your question?

swe
  • 1,416
  • 16
  • 26
  • user3635201 wants to add all adress' in one row only,so this answer seems incorrect. this will give you number of rows eqaus to number of address' – Mudassir Nov 05 '14 at 12:10
  • 1
    to those, who voted this answer down: At the time this answer was given the question was not clear. Please upvote to 0. Thanks. – swe Nov 05 '14 at 14:48
-1

the solution in mssql is the keyword PIVOT.

I'll add an example soon

swe
  • 1,416
  • 16
  • 26