combine column is same table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » combine column is same table

combine column is same table

Rate Topic

Display Mode

Topic Options

Author

Message

smmr_emon

Posted Monday, December 03, 2012 3:53 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 8:17 AM
Points: 1, Visits: 5

hi,

i've 2 tables. table 1 is like

col1 col2 col3
x y z

in the other table the items(x, y, z) are like this

col1 col2 col3 col4 col5
x 1 <empty> 1 1
y <empty> 1 1 <empty>
z 1 1 1 1

i want to join the 2 tables and show the data in the following format

x 1,1,1
y 1,1
z 1,1,1,1

how can i do that? and is it possible to do in sql7?

sorry for bad formatting

thanx

Post #1392173

Sean Lange

Posted Tuesday, December 04, 2012 7:56 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 8,622, Visits: 8,263

Hi an welcome to SSC. It is impossible to provide any detailed help based on your post because there just aren't any details. I think that maybe what you want is called a cross tab. You can read about the technique by following the link in my signature. If that is not what you are looking for or you need specific help please read the link in my signature about best practices when posting questions. It will help guide you through how to make your post effective so you get assistance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1392505

tcv 11632

Posted Tuesday, December 04, 2012 8:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:18 AM
Points: 1, Visits: 9

This is quite a bad situation, however perhaps this might help.... I am not sure it works (I haven't tested it), and it is a dirty solution, but perhaps it helps you on the way. Good luck!

SELECT TAB.COL
, ISNULL(COL2, ISNULL(COL3, ISNULL(COL4, COL5))) "COL2"
, CASE
WHEN COL2 IS NULL
THEN ISNULL(COL4, COL5)
ELSE ISNULL(COL3, ISNULL(COL4, COL5))
END "COL3"
, CASE
WHEN COL2 IS NULL
THEN COL5
WHEN COL3 IS NULL
THEN ISNULL(COL4, COL5)
ELSE COL4
END "COL4"
, CASE
WHEN COL2 IS NULL OR COL3 IS NULL OR COL4 IS NULL
THEN NULL,
ELSE COL5
END "COL5"
FROM TABLE2 T2
JOIN
(
SELECT COL1 "COL"
FROM TABLE1
UNION
SELECT COL2 "COL"
FROM TABLE1
UNION
SELECT COL3 "COL"
FROM TABLE1
) TAB
ON
T2.Col1=TAB.COL

Post #1392550

« Prev Topic | Next Topic »

Permissions