select data and display in different coulumns

Question

select data and display in different coulumns

Vundela

Mr or Mrs. 500

Points: 591
More actions
June 23, 2008 at 11:43 am

#187400

I have four tables
customer(pk_Customerid,name,address,fk_phonetypeid)
customerphone(pk_customerphoneid,fk_customerid,fk_phoneid)
phone(pk_phoneid,fk_phonetypeid,phone)
phonetype(pk_phonetypeid)
phonetypeid : 1,2,3
where 1 represents mobile,2 represent landline and 3 represents others or fax.
In base table phone - phone,landline,fax are stored in the single column ie; phone and are identified by phonetypeid column, but for display i want those numbers to be in different columns.
Now i want to write a stored procedure which retrieve the customer details like:
customerid name mobile landline others or fax
----------------------------------------------------------
based on input parameter @customerid
Help Me!!!

Viewing 11 posts - 1 through 10 (of 10 total)

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

mtassin SSC-Insane Points: 23099 More actions · Answer 1

kiran (6/23/2008)
customer(pk_Customerid,name,address,fk_phonetypeid)
customerphone(pk_customerphoneid,fk_customerid,fk_phoneid)
phone(pk_phoneid,fk_phonetypeid,phone)
phonetype(pk_phonetypeid)

The structure of these tables isn't exactly the best... either fk_phonetypeid should be in customerphone, or there should be a master customer table. Otherwise you have to operate based on the name being the Identifier of a customer, and it's just not good.

See normal forms or Domain Key Normal Form on how to correct.

--How to do it with the structure above (without actual create statements I can't really test this)

SELECT DISTINCT

name,

address

INTO #customers

SELECT

a.name,

a.address,

mobilephone = d.phone,

landline = f.phone,

otherorfax = h.phone

FROM

#customers a JOIN customer b ON

a.name = b.name and

a.address = b.address

LEFT JOIN customerphone c ON

b.pk_customerid = c.fk_customerid

JOIN phone d ON

c.fk_phoneid = d.pk_phoneid and

d.fk_phonetypeid = 1

LEFT JOIN customerphone e ON

b.pk_customerid = e.fk_customerid

JOIN phone f ON

e.fk_phoneid = f.pk_phoneid and

f.fk_phonetypeid = 2

LEFT JOIN customerphone g ON

b.pk_customerid = g.fk_customerid

JOIN phone h ON

g.fk_phoneid = h.pk_phoneid and

h.fk_phonetypeid = 3

How I'd correct it

CREATE TABLE customer (

customer_id INTEGER IDENTITY(1,1) PRIMARY KEY,

customer_name VARCHAR(120),

customer_address VARCHAR(250),

)

CREATE TABLE phonetype(

phonetypeid tinyint IDENTITY(1,1) PRIMARY KEY,

phonetype_desc varchar(50) NOT NULL)

CREATE TABLE customer_phone(

cust_ph_id INTEGER IDENTITY(1,1) PRIMARY KEY,

cust_ph_type TINYINT FOREIGN KEY REFERENCES phonetype(phonetypeid) NOT NULL,

cust_ph_num VARCHAR(20) NOT NULL,

customer_id INTEGER FOREIGN KEY REFERENCES customer(customer_id) NOT NULL

)

/* Sample Data */

INSERT INTO PHONETYPE(phonetype_desc) VALUES('Mobile')

INSERT INTO PHONETYPE(phonetype_desc) VALUES('Land Line')

INSERT INTO PHONETYPE(phonetype_desc) VALUES('Fax or Other')

INSERT INTO CUSTOMER(customer_name,customer_address) VALUES ('Information','1 Somewhere lane Some City, SW ?????')

INSERT INTO CUSTOMER_PHONE (cust_ph_type,cust_ph_num,customer_id) VALUES(1,'800-555-1212',1)

INSERT INTO CUSTOMER_PHONE (cust_ph_type,cust_ph_num,customer_id) VALUES(2,'888-555-1212',1)

INSERT INTO CUSTOMER_PHONE (cust_ph_type,cust_ph_num,customer_id) VALUES(3,'877-555-1212',1)

/* Query Below*/

SELECT

a.customer_name,

b.cust_ph_num,

c.cust_ph_num,

d.cust_ph_num

FROM

customer a LEFT JOIN customer_phone b ON

a.customer_id = b.customer_id

JOIN phonetype b1 ON

b.cust_ph_type = b1.phonetypeid

and b1.phonetype_desc = 'Mobile'

LEFT JOIN customer_phone c ON

a.customer_id = c.customer_id

JOIN phonetype c1 ON

c.cust_ph_type = c1.phonetypeid

and c1.phonetype_desc = 'Land Line'

LEFT JOIN customer_phone d ON

a.customer_id = d.customer_id

JOIN phonetype d1 ON

d.cust_ph_type = d1.phonetypeid

and d1.phonetype_desc = 'Fax or Other'

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Vundela Mr or Mrs. 500 Points: 591 More actions · Answer 2

Vundela

Mr or Mrs. 500

Points: 591

June 24, 2008 at 9:28 am

#833386

Thank You!

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

Hey Mark! Nice avatar!!!! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 4

Thanks! 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

Hi,

In simnple way you can write queries with the Group by Clause and CASE statements.

SELECT c.fk_customerid,

MAX(DECODE (pfk_phonetypeid,1, phone) AS mobile_Phone,

MAX(DECODE (pfk_phonetypeid,2, phone) AS LandLine_Phone,

MAX(DECODE (pfk_phonetypeid,3, phone) AS Fax_Phone,

FROM customerphone C,PHONE P

where c.fk_phoneid = p.pk_phoneid

group by c.fk_customerid

DECODE is in Oracle, you can use CASE in SQL Server to achive it.

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 6

Since Oracle 8.3, you also can use Case... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jthomastx Newbie Points: 6 More actions · Answer 7

I actually have a similar question. I have a table that contains customer_id, customer_Fname, Contact_date, Contact_type. In running a query showing all contact dates / times for each customer_id, the result will be all customer_id with no contact displaying null values but those with multiple contacts will produce multiple rows for each contact_date/Contact_type.

example: Customer_id, Contact_date, Contact_type

01234 10/10/2008 phone call

01234 10/15/2008 interview

23456 09/01/2007 face2face

23456 09/28/2008 phone call

My goals is to create a report showing each instance of customer contact_type/date as an individual column heading.

Example

Customer_id, Contact_date01, Contact_type01, Contact_date02, Contact_type02,.....

01234 10/10/2008 phone call 10/15/2008 interview

23456 09/01/2007 face2face 09/28/2008 phone call

Any thoughts would be appreciated.

JT

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 8

jthomastx (6/26/2008)
I actually have a similar question. I have a table that contains customer_id, customer_Fname, Contact_date, Contact_type. In running a query showing all contact dates / times for each customer_id, the result will be all customer_id with no contact displaying null values but those with multiple contacts will produce multiple rows for each contact_date/Contact_type.
example: Customer_id, Contact_date, Contact_type
01234 10/10/2008 phone call
01234 10/15/2008 interview
23456 09/01/2007 face2face
23456 09/28/2008 phone call
My goals is to create a report showing each instance of customer contact_type/date as an individual column heading.
Example
Customer_id, Contact_date01, Contact_type01, Contact_date02, Contact_type02,.....
01234 10/10/2008 phone call 10/15/2008 interview
23456 09/01/2007 face2face 09/28/2008 phone call
Any thoughts would be appreciated.
JT

See the following thread... well worth the read... many folks contributed to something that is article worthy for both SQL Server 2000 and 2005... in a nutshell, Cross Tabs win over pivots.

http://www.sqlservercentral.com/Forums/Topic521489-338-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jthomastx Newbie Points: 6 More actions · Answer 9

Jeff,

I appreciate the article. Will spend the next hour reviewing all the different methods. Excellent - Thank you.

JT

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 10

Its nice one.

Thanks -- Vj

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers