Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query help Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 2:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:24 AM
Points: 3,537, Visits: 2,647
In a table, I have value like this:

1A
2A

I want data like this:
1C
1D
2C
2D


that is for each value of A in table, i want two records with C and D.
Post #1408240
Posted Thursday, January 17, 2013 3:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 1,780, Visits: 5,645
sqlnaive (1/17/2013)
In a table, I have value like this:

1A
2A

I want data like this:
1C
1D
2C
2D


that is for each value of A in table, i want two records with C and D.


SELECT newColumn
FROM yourTable as yt
CROSS APPLY (
SELECT LEFT(yt.yourColumn,1)+'C'
UNION ALL
SELECT LEFT(yt.yourColumn,1)+'D'
) AS new(newColumn)

Without table definitions and sample data, this is just a sample showing how CROSS APPLY can be used to expand 1 row to many...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1408259
    Posted Thursday, January 17, 2013 4:51 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: 2 days ago @ 6:24 AM
    Points: 3,537, Visits: 2,647
    Cool. Thanks. I was trying this by creating temp table and then making CROSS JOIN. But definitely it's better.
    Post #1408334
    Posted Thursday, January 17, 2013 5:32 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Yesterday @ 6:12 PM
    Points: 3,609, Visits: 5,220
    This should also work:

    SELECT newColumn
    FROM yourTable as yt
    CROSS APPLY (
    VALUES (LEFT(yt.yourColumn,1)+'C')
    ,(LEFT(yt.yourColumn,1)+'D') new (newColumn)





    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
    Since random numbers are too important to be left to chance, let's generate some!
    Learn to understand recursive CTEs by example.
    Splitting strings based on patterns can be fast!
    Post #1408681
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse