SQLTeam.com | Weblogs | Forums

SQL script to get DISTINCT values of Product

tsql
homework

#1

In Table T there are few columns of which 2 columns are
related to Product – Product Code (col A) and Plan Code (col B). Col A contains
only one value at one time per record. However, Col B can contain more than one
Plan code in a single row. The Plan codes are spread across col B which has
length of 500 chars. Also, Plan Code in col B is appears for every first 3
characters of every 18 characters read. E.g aklsjjhvkjwejbivpwepbvvkbvsa….
, akl and epb are 2 plan codes here in the given string of col B.

Please write a SQL script to get DISTINCT values of Product
Code and Plan Code from Table T. For instance if table T has a record

Col A

Col B

AAB

aklsjjhvkjwejbivpwepbvvkbvsa…

The script
should result in

Col A

Col B

AAB

akl

AAB

epb


#2

What have you tried so far?


#3

I posted as new topic.. this is new SQL query ..so I don't how to write


#4

If you have idea about this question. please help me to write


#5

Looks like homework to us. If we do this for you then you won't have learnt anything ... so we need you to tell us what you have done, what you think you might do, ask questions to help you discover how to etc. etc. There is not point us just doing the query for you ...


#6

Hi Kristen,

I have tried a lot but I couldn't figure out .. correct one and same time I never cross this kind of SQL query from my past experience that's why..

The below one is what I done so far..

Select * from Table T ( insert into table T value(substring(‘aklsjjhvkjwejbivpwepbvvkbvsa’,1,3)

Union all Insert to
table T value(substring(‘aklsjjhvkjwejbivpwepbvvkbvsa’,19,3))


#7

Here my doubt is that after selecting of character from same string and row , inserting of value into second row..


#8

What I think you are trying to do is:

SELECT ColA, substring(ColB,1,3)
FROM T
UNION ALL
SELECT ColA, substring(ColB,19,3)
FROM T

but note that that will only partially answer the question

"col B has length of 500 chars ... col B appears for every first 3 characters of every 18 characters" so for data that is longer than the example a more complex solution is needed.

Personally I would use a Tally Table (which you could Google).

This example / question is quite difficult, and your sample answer suggests to me that it is beyond your level of skill, so my advice would be to discuss this with your Tutor / Teacher. Your Tutor / Teacher will then be able to use their experience of teaching you to gauge where the gaps are in your knowledge and help you to understand the issues and gain the knowledge and skill required. It is difficult to do that on a forum as we have no knowledge of what you have learnt so far and and so on.


#9

Yeah Kristen,

Thank you a lot

You are right I am still learning stage in Sql query