question

Binod avatar image
Binod asked

Two Group contain similar record in one specific colummn

Respected All, Please share your expertise on this. i have a table MY First Table is having SKU without suffix BASE SKU PRODUCT 1 PRODUCT 2 PRODUCT 3 ABC123 Phone Pen Note ABC123 Phone Pen Note star XUZTUV MOUSE KEYBOARD WIRELESS XUZTUV MOUSE KEYBOARD WITH WIRE LMNOP123 MOUSE KEYBOARD WIRELESS LMNOP123 MOUSE KEYBOARD WITH WIRE My 2nd Table is having SKU with suffix GR and TN SKU with Suffix GR ABC123GR Phone Pen Note ABC123GR Phone Pen Note star SKU with Suffix TN ABC123TN Phone Pen Note ABC123TN Phone Pen Note star SKU with Suffix GR XUZTUVGR MOUSE KEYBOARD WIRELESS XUZTUVGR MOUSE KEYBOARD WITH WIRE SKU with Suffix TN XUZTUVTN MOUSE KEYBOARD WIRELESS XUZTUVTN MOUSE KEYBOARD WITH WIRE Odd Record with suffix GR Should not be in output as not available with all option **LMNOP123GR,MOUSE,KEYBOARD,WIRELESS** I Want to pull only those records when all item in product 3 for given Base SKU is also present with SKU With Suffix GR and TN Here Output should not contain LMNOP123GR as it not available with all option Present in Base SKU LMNOP123 Thanks a Lot. i just expand example for clarity. Thanks Again.
sql-server-2008
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
Will you provide some data that supports what you are asking? Maybe I am misinterpreting the question, but the question is not supported by the data you have provided. And it would also be helpful to provide an example of what you need the output to look like.
0 Likes 0 ·
Binod avatar image Binod commented ·
Hi Jason, i just modified the question. Thank You.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Binod Why do you have 2 records for each base SKU in your first table? This is somewhat strange because one would expect every base SKU to be unique (with only one record per base SKU, not 2 records). Please let me know. Oleg
0 Likes 0 ·
Binod avatar image Binod commented ·
Hi Oleg. Client is not using SKU as primary key, they have identity column as primary. we have little access to Modify their table. Please help.
0 Likes 0 ·
Binod avatar image Binod commented ·
any luck? Gentlemen.
0 Likes 0 ·
Show more comments

1 Answer

·
@SQLShark avatar image
@SQLShark answered
Hi Binod, You can do a simple replace to remove the GR if that is what you need. REPLACE(SKU, 'GR', '') AS 'SKU' What is it you're trying to achieve?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLShark avatar image @SQLShark commented ·
based on the changes: DECLARE @Example TABLE ( SKU VARCHAR(20) , COVER INT , CORK INT , Year INT , COMMENT VARCHAR(50) ) INSERT INTO @Example ( SKU, COVER, CORK, Year, COMMENT ) VALUES ( 'ZZX105GR', 63, 830, 2004, 'wish Drive' ) , ( 'ZZX105GR', 63, 830, 2004, 'Fish Drive' ) , ( 'ZZX105', 63, 830, 2004, 'wish Drive' ) , ( 'ZZX105', 63, 830, 2004, 'Fish Drive' ) SELECT DISTINCT REPLACE(SKU, 'GR', '') AS 'SKU' , COVER , CORK , Year , COMMENT FROM @Example
1 Like 1 ·
Binod avatar image Binod commented ·
Thanks Terry, but i think i was unable to explain my question properly, i have modified my doubt. Please help.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.