Combine data of 3 tables into a single row

Question

Combine data of 3 tables into a single row

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

zaleeu SSChasing Mays Points: 602 More actions · Answer 1

Welsh Corgi (9/12/2011)
zaleeu (9/12/2011)
Maximum of 6 Fruit.
What if they decided that they want to start selling more than six types of fruit? 😀
What if they decided that he wanted to sell vegetables? I guess that they would be out of luck?

;-):-P:-D Humor me 🙂

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 2

Hi,

This is another way...

SELECT DISTINCT NAME,I.FRUITID, FRUIT

INTO #TEMP1

FROM FruitDistibutors F

INNER JOIN Interlink I

ON F.DistributorID = I.DistributorID

INNER JOIN FruitNames N

ON FRUITID = ID

SELECT * FROM #TEMP1

pivot (

MAX (FRUIT) for FRUITID

IN ([1],[2],[3])

) AS P

Regards

PG

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

zaleeu (9/12/2011)
Welsh Corgi (9/12/2011)
zaleeu (9/12/2011)
Maximum of 6 Fruit.
What if they decided that they want to start selling more than six types of fruit? 😀
What if they decided that he wanted to sell vegetables? I guess that they would be out of luck?
;-):-P:-D Humor me 🙂

Not a real world scenario.:-P

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

zaleeu SSChasing Mays Points: 602 More actions · Answer 4

Hi PG,

I still get the Items in the wrong place.

NAMEDistributorID123

Peter1 1 NULLNULL

Paul2 NULL2 NULL

:hehe:

palash.gorai (9/12/2011)
Hi,
This is another way...
SELECT DISTINCT NAME,I.FRUITID, FRUIT
INTO #TEMP1
FROM FruitDistibutors F
INNER JOIN Interlink I
ON F.DistributorID = I.DistributorID
INNER JOIN FruitNames N
ON FRUITID = ID
SELECT * FROM #TEMP1
pivot (
MAX (FRUIT) for FRUITID
IN ([1],[2],[3])
) AS P
Regards
PG

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 5

Hi Zaleeu,

I have used the same example as mentioned in this forum and ran at my end. Following is the result

NAME123

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PaulNULLOrangesNULL

PeterApplesNULLPlums

(2row(s)affected)

Can you share your code?

Thanks

PG

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

zaleeu (9/12/2011)
Welsh Corgi (9/12/2011)
zaleeu (9/12/2011)
Maximum of 6 Fruit.
What if they decided that they want to start selling more than six types of fruit? 😀
What if they decided that he wanted to sell vegetables? I guess that they would be out of luck?
;-):-P:-D Humor me 🙂

Since the Distributer is only allowed to carry 6 types of fruit you could add a Check Constrint if someone tries to enter a fruit that is not on the list. :hehe:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

zaleeu SSChasing Mays Points: 602 More actions · Answer 7

Hi,

Your output is correct. I got confused with my own fictional tables. 🙂

But I am still looking for an output like below.

Peter| Apples | Plums ( No NULL Value inbetween )

:w00t:

palash.gorai (9/12/2011)
Hi Zaleeu,
I have used the same example as mentioned in this forum and ran at my end. Following is the result
NAME123
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PaulNULLOrangesNULL
PeterApplesNULLPlums
(2row(s)affected)
Can you share your code?
Thanks
PG

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

Hi Zaleeu

But I am still looking for an output like below.
Peter| Apples | Plums ( No NULL Value inbetween )

can you please clarify....do the results have to be in separate columns or just a single column output with a separator (like | or ::) between the results?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

zaleeu SSChasing Mays Points: 602 More actions · Answer 10

Sorry for being ambigious ....

Ignore the | characters , that was just to seperate the data.

So, I need it to be

Paul Oranges

----------------

Peter Apples Plums

-------------------

Thanks !

:w00t:

J Livingston SQL (9/12/2011)
Hi Zaleeu
But I am still looking for an output like below.
Peter| Apples | Plums ( No NULL Value inbetween )
can you please clarify....do the results have to be in separate columns or just a single column output with a separator (like | or ::) between the results?

zaleeu SSChasing Mays Points: 602 More actions · Answer 11

LOLz .

Nope its not.

Its actually a learning curve ( Cold Coffee nailed it ).

I am using this ficticious example so I can apply it to real live data.

So, once I get the Fruit part right , then i can transfer the code to real live data.

;-):-D

Welsh Corgi (9/12/2011)
This is not a homework assignment, right.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

I do not seriously believe that CC believes that this was not Homework. He is smarter than that.

I have been writing Business Requirements Documents for years and the Problem as you stated does not resemble any real world problem. It defies all logic.

If you have six fruit then perhaps you can make fruit cake for your party.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

zaleeu SSChasing Mays Points: 602 More actions · Answer 13

Okay , let me give you another example.

In BANKING :

Joe Bloggs has 3 different type of accounts at the same bank.

He has a 1) Credit Card 2) Cheque Account 3) Investment account.

Polly Fruitcake has only 2 accounts.

She has 1) Credit Card 2) Savings Account.

Table 1 = Customerdata

CustomerID, FirstName, Lastname, Account Type

00Z1 , Joe, Bloggs, 1

00F9, Polly, Fruitcake, 1

00Z1 , Joe, Bloggs, 2

00F9, Polly, Fruitcake, 4

00Z1 , Joe, Bloggs, 3

Table 2 = AccountLink

CustomerID , Account Type

00Z1 , 1

00Z1, 2

00Z1, 3

00F9, 1

00F9, 4

Table 3 = Account Types

1, Credit Card

2, Cheque Account

3, Investment Account

4, Savings Account

Output needed :

Polly Fruitcake, Credit Card , Savings Account

Joe Blogs, Credit Card, Cheque Account, Investment Account.

:w00t:

Welsh Corgi (9/12/2011)
I do not seriously believe that CC believes that this was not Homework. He is smarter than that.
I have been writing Business Requirements Documents for years and the Problem as you stated does not resemble any real world problem. It defies all logic.
If you have six fruit then perhaps you can make fruit cake for your party.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 14

zaleeu (9/12/2011)
Sorry for being ambigious ....
Ignore the | characters , that was just to seperate the data.
So, I need it to be
Paul Oranges
----------------
Peter Apples Plums
-------------------

stiil not clear...........

is this the expected output ...eg a table of results?

USE [tempdb]

GO

CREATE TABLE [dbo].[TBL](

[Cust Id] [nvarchar](50) NULL,

[col1] [nvarchar](50) NULL,

[col2] [nvarchar](50) NULL,

[col3] [nvarchar](50) NULL,

[col4] [nvarchar](50) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[TBL] ([Cust Id], [col1], [col2], [col3], [col4]) VALUES (N'Polly Fruitcake', N' Credit Card ', N' Savings Account', NULL, NULL)

INSERT [dbo].[TBL] ([Cust Id], [col1], [col2], [col3], [col4]) VALUES (N'Joe Blogs', N' Credit Card', N' Cheque Account', N' Investment Account.', NULL)

INSERT [dbo].[TBL] ([Cust Id], [col1], [col2], [col3], [col4]) VALUES (N'Bilbo Baggins', N' Savings Account', N' Investment Account.', NULL, NULL)

INSERT [dbo].[TBL] ([Cust Id], [col1], [col2], [col3], [col4]) VALUES (N'G Sus', N' Cheque Account', N' Credit Card ', N' Investment Account.', N' Savings Account')

SELECT * FROM TBL

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 15

Can I submit my homework assignments here if I do not admit that it is homework? :hehe:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/