please help me with a sql query for comma seperated field?

Question

there are 3 fields (ID, Name, Drugs)in a table named details
the value stored in drugs column is multiple drugs seprated by comma eg 'drug1,drug2, drug3'
and there can be no of rows with values

Eg.
ID Name Drugs
01 name1 Drug1,Drug2,Drug3
02 name2 Drug4,Drug5,Drug6

how can convert the comma separated value in column drugs to column in sql
so that i can get the values in collumn

i.e
ID Name Drugs
------------------------------------
01 name1 Drug1
01 name1 Drug2
01 name1 Drug3
02 name2 Drug4
02 name2 Drug5
02 name2 Drug6
please help me with a detailed sql code

Answer 1

What's the database and what tool are you using to write the report? Note: The programmer might be saying it's impossible due to other factors besides the SQL. The SQL should be simple, if there are no limitations you've forgotten to mention. Or he might just be discouraging you from doing something that will be very slow, or might even break the system.

Answer 2

Here's a solution in Visual Basic:



Put the sub into a module and execute



Sub TransformTable()



  Dim strSQL As String

  Dim strName As String

  Dim rstX As ADODB.Recordset

  Dim d



  Set rstX = New ADODB.Recordset

  strSQL = "SELECT Id, Name, Drug FROM tblDrug;"

  rstX.Open Source:=strSQL, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, ActiveConnection:=CurrentProject.Connection



  With rstX

   .MoveFirst

    Do While (Not .EOF)

      strName = .Fields("Name")

      ID = .Fields("Id")

      d = Split(.Fields("Drug"), ",")

     .Fields("Drug") = d(0)           ' adjust current field to one drug

      i = 1

      Do While (i <= UBound(d))

       .AddNew          ' Add a new row for each extrs drug

       .Fields("Id") = ID

       .Fields("Name") = strName

       .Fields("Drug") = d(i)

       .Save

        i = i + 1

      Loop

     .MoveNext



    Loop

  End With

End Sub

Answer 3

If it is impossible to change table structure

it is possible to realise through stored procedure...

Answer 4

your table structure is wrong, use mater detail tables for this situation. drugs will be in another child table

Answer 5

select id, name, drug from Drugs D

CROSS JOIN

(select 'Drug1' as drug

union all select 'Drug2'

union all select 'Drug3'

union all select 'Drug4'

union all select 'Drug5'

union all select 'Drug6'

--all your drugs here

) X

where drugs like '%' + drug + '%'