Complex SQL query in SQL Server 2008 tables

5 pts.
Tags:
SQL
SQL Query
SQL Server
SQL Server 2008
SQL Server tables
Sir, i have 2 tables , first table column one is 1,2,3,4, and column two is x,y,w,z and second table  first column of 1,2,3 second column is (1;2),(1;2;3;4),(2,4), i wand to display the result as in first colum 1,2,3 and second column as (x,y),(x,y,w,z),(w,z)

please help me in this regard



Software/Hardware used:
sql server 2008

Answer Wiki

Thanks. We'll let you know when a new response is added.

That would be a very inefficient query, and would require functions to figure out the values to be displayed.

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    The question first needs to be clarified. 1. How are the tables related? I.e., if the first table has "1,2,3,4" in column 1, and the second table has "1,2,3" in column 1, what is the rule used to join the tables? 2. The example format has an error (or two) - either the example pattern should end with "...(y,z)" or the example output should end with "...(3,4)". Otherwise, the pattern to output relationship makes no sense. Any attempt to do this strictly in T-SQL without writing a function would be incredibly inefficient. I would think the best approach would be to write a function that took the pattern and source data and returned the result string, e.g.:
    set @output = MyFunction( '(1;2),(1;2;3;4),(2,4)' , 'x,y,w,z' )
    The function would return '(x,y),(x,y,w,z),(y,z)'. Then, your query would look something like:
    select 
       tblpattern.col1,
       MyFunction ( tblpattern.col2,  tbldata.col2 )
    from
       FirstTable tbldata
           join SecondTable tblpattern on tblpattern.?what? = tbldata.?what?
    The function itself wouldn't be that difficult.
    3,830 pointsBadges:
    report
  • carlosdl
    Yes, the data provided seems to be incorrect. It would probably be inefficient using functions as well, so IMO the best solution would be redesigning the second table.
    69,920 pointsBadges:
    report
  • Darryn
    The data looks correct, they are joining the values in table2.col2 with table1.col1 and using table1.col2 as the replacement value. However, I agree with both statements provided, it would be best to redesign the table, but a function can also be written that separates the values in the brackets, does a lookup for the values, and then puts the values back into a bracketed return value.
    765 pointsBadges:
    report
  • carlosdl
    "The data looks correct, they are joining the values in table2.col2 with table1.col1 and using table1.col2 as the replacement value." I don't think so. This is the first table:
    1	x
    2	y
    3	w
    4	z
    So, (2,4) should be (y,z), not (w,z) But it doesn't really matter. It doesn't affect what has been suggested.
    69,920 pointsBadges:
    report
  • TomLiotta
    So, (2,4) should be (y,z), not (w,z) Unless the comma and the semi-colons make a difference -- but that would make it even more questionably complex. I can almost see a possibility of a variation of a recursive CTE being used by a UDF to return the results, but I can't guess why anyone would want this to work in SQL. I've seen stranger things though. (I couldn't guess for those either.) Tom
    125,585 pointsBadges:
    report
  • Kccrosser
    As long as the function is only used in the Select portion of the statement (i.e., only applied to the rows already selected by the Where clause), this wouldn't be that inefficient. Even if the tables were redesigned to split the values into separate columns, the computational complexity/overhead would be similar.
    3,830 pointsBadges:
    report
  • carlosdl
    I agree Kccrosser, but a better table design would simplify this query and probably many others in the future.
    69,920 pointsBadges:
    report
  • Asdadfsdffdsdf
    select m.category ,(select case c.name when '(1;2)' then '(x,y)' when '(1;2;3;4)' then '(x,y,w,z)' when '(2,4)' then '(w,z)' end as name from Prod_Category c where c.id=m.category ) name from Prod_Master m inner join Prod_Category c on c.id=m.category
    10 pointsBadges:
    report
  • Kccrosser
    To the last poster (with the unimaginative name): Suggest you read the full thread before posting. The problem wasn't to solve the one specific case, but to be data-driven from control tables. Explicitly converting through a SQL case statement would potentially require enumerating all possible combinations of 1..4 variables in any combination (and order). For the simple case of 4 variables, that would be 64 case branches. Adding a fifth variable would require 325 case branches.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following