December 3, 2012 at 3:53 pm
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
December 4, 2012 at 7:56 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 4, 2012 at 8:38 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply