Sql Query Help Needed

[krish5555]

Hi Guys

  I want to split one column into 4 columns as shown below.

parameters

PanelPanel=AB01110201&YearYear=2013&Source=1&UserID=aad2779

PanelPanel=AB12100173&YearYear=2013&Source=1&UserID=aac6440

it should be display as 

panel                      yearyear                source              userid

AB01110201           2013                            1                    aad2779

AB12100173           2013                            1                    aac6440

 

there will be thousands of rows in the column. Can anybody help how to split it as shown.The length may very from row to row

 

Thanks in advance.

[sunnygm]

use pivot function..

[ChampakDas]

think_ww

[ramu]

Regexp_substr function use cheyyi...

[Itshulk]

Check this 

 

select substr(column_name,1,10) as part1,

 substr(column_name,12,15) as part2,

 substr(column_name,17,18) as part3,

 substr(column_name,20,25) as part4

 from table_name

[alpachinao]

help

[jaffanajeffada]

if the format of the data stays put, below sql will give you desired output...

 

replace COLUMN_NAME and TABLE_NAME with actual names

 

SELECT COLUMN_NAME, 

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,1)+1,INSTR(COLUMN_NAME,'&',1,1)-INSTR(COLUMN_NAME,'=',1,1)-1)) PANEL,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,2)+1,INSTR(COLUMN_NAME,'&',1,2)-INSTR(COLUMN_NAME,'=',1,2)-1)) YEAR,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,3)+1,INSTR(COLUMN_NAME,'&',1,3)-INSTR(COLUMN_NAME,'=',1,3)-1)) SOURCE,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,4)+1)) USERID

FROM  TABLE_NAME

 

[Darling999]

Pivot is the Best option for you

[k2s]

:3D_Smiles:

[mtkr]

 

if the format of the data stays put, below sql will give you desired output...

 

replace COLUMN_NAME and TABLE_NAME with actual names

 

SELECT COLUMN_NAME, 

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,1)+1,INSTR(COLUMN_NAME,'&',1,1)-INSTR(COLUMN_NAME,'=',1,1)-1)) PANEL,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,2)+1,INSTR(COLUMN_NAME,'&',1,2)-INSTR(COLUMN_NAME,'=',1,2)-1)) YEAR,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,3)+1,INSTR(COLUMN_NAME,'&',1,3)-INSTR(COLUMN_NAME,'=',1,3)-1)) SOURCE,

  TRIM(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'=',1,4)+1)) USERID

FROM  TABLE_NAME

 

 

 

bl@st                     bl@st

[ramu]

Pivot is the Best option for you

kodhiga example raasipettochuga

[jaffanajeffada]

thread started, was that helpful?

[Hitman]

thread started, was that helpful?

 

CITI#H@

[BC509]

Create a USER defined function that accepts that querystring values column and returns you with DataRow.

[BC509]

 SELECT *

  FROM (

   (select 

    SUBSTRING(Value,CHARINDEX('=',Value)+ 1,LEN(Value)- CHARINDEX('=',Value))[Value]

    ,SUBSTRING(Value,0,CHARINDEX('=',Value))[Parameter] 

    from 

    [DATABASE]..Split('PanelPanel=AB01110201&YearYear=2013&Source=1&UserID=aad2779','&') 

    ))AS [DATA]

    PIVOT 

    (

    MAX(VALUE)

    FOR Parameter IN ([PanelPanel],[YearYear],[Source],[UserID]) 

    )AS [PIVOTDATA]