Split field into multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split field into multiple columns

Author

Topic

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 10:21:40

I have a query that returns several fields of which one is a long string that needs to be split into several columns. The string varies in length and the column names are determined by the string(i.e. 'version='. The string is delimited by '&'. Here is a sample string.

version=0200&muserID=48580744&payment_comprehensive=1&payment_summary=0&business_background=0&trade_summary=0&trade_detail=0&public_filings=0&financial_relationships=0&ucc_filings=0&transpo_score=0&office_score=0&user=minuxtra.com&password=

I have a script that can split the string into rows. How can I split this string, get the column names, and display all of the fields, including the other columns required?

Pivot? Can it be used if I don't know the column names, nor the number of columns?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:24:29

are you using sql 2005?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:25:04

also will column names be alwyas same?

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 10:25:20

Yes. I am using SQL 2005 64bit

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 10:27:06

Yes, the output column names will be the same, but many not be populated by the string everytime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:33:49

[code]
SELECT [version],[muserID],... other columns here
FROM
(
SELECT f.ID,PARSENAME(REPLACE(f.Val,'=','.'),2) AS Col,PARSENAME(REPLACE(f.Val,'=','.'),1) AS Val
FROM dbo.ParseValues(@String,'&') f
)m
PIVOT ( MAX(Val) FOR Col IN ([version],[muserID],... other columns here))p
[/code]

parsevalues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113563

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 10:36:38

Will this work if the version or muser fields are not in the input string? THe input string can be different everytime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:38:39

yup..it will work...it will return NULL for them.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 10:42:05

I will try it. Thanks. This looks so much simpler than I thought. Will let you know the results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:47:02

welcome

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 10:58:35

I am not seeing where the information is coming from. Where would I put the from statement to retrieve the fields?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 11:00:29

@String represents variable containing the long list of columnnames & values

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 11:37:23

I added the function ParseValues to my table-valued functions. Does this query make sense? Getting error: Incorrect syntax near '.'.

select
phra_subid,
(
SELECT [user], version, paynet_id, payment_comprehensive, payment_summary, business_backround,
trade_summary, trade_detail, public_filings, financial_relationships, ucc_filings, transpo_score,
master_score, legal_name, agri_score, construction_score
FROM
(
SELECT f.ID,PARSENAME(REPLACE(f.Val,'=','.'),2) AS Col,PARSENAME(REPLACE(f.Val,'=','.'),1) AS Val
FROM dbo.ParseValues(a.phra_request_querystring,'&') f
)m
PIVOT
(
MAX(Val) FOR Col IN ('user', 'version', 'paynet_id', 'payment_comprehensive', 'payment_summary', 'business_backround',
'trade_summary', 'trade_detail', 'public_filings', 'financial_relationships', 'ucc_filings', 'transpo_score',
'master_score', 'legal_name', 'agri_score', 'construction_score')
)p
)
from dbo.phr_audit a where
(@subid = -1 or phra_subid = @subid)
and (phra_auditdt between @from and @thru)
and (@reqUrl = 'ALL' or phra_request_url = @reqURL)
and phra_request_url <> '/phr_generate.asp';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 11:58:15

you dont nee the '' inside IN clause. just use MAX(Val) FOR Col IN (user, version,...)

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 12:12:20

I removed the quotes, but I am still getting the same error. Getting error: Incorrect syntax near '.'.

Why do you replace the '=' with '.'? Just trying to understand the logic. I am a rookie.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:15:53

thats for PARSENAME to seperate the two words.
b/w where does a.phra_request_querystring come from? is it a table field? then where's the table reference?

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 12:19:46

The table it is getting the info from at the end of the query, dbo.phr_audit. All of the phra_* fields are coming from that table, including the string value. THe other fields in the from statement that begin with @ are parameters from aspx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:19:56

i think query should be this


SELECT [user], version, paynet_id, payment_comprehensive, payment_summary, business_backround,
trade_summary, trade_detail, public_filings, financial_relationships, ucc_filings, transpo_score, 
master_score, legal_name, agri_score, construction_score
FROM
(
SELECT a.PK,PARSENAME(REPLACE(f.Val,'=','.'),2) AS Col,PARSENAME(REPLACE(f.Val,'=','.'),1) AS Val
FROM from dbo.phr_audit a 
CROSS APPLY dbo.ParseValues(a.phra_request_querystring,'&') f
where (@subid = -1 or phra_subid = @subid)
and (phra_auditdt between @from and @thru)
and (@reqUrl = 'ALL' or phra_request_url = @reqURL)
and phra_request_url <> '/phr_generate.asp'
)m
PIVOT 
(
MAX(Val) FOR Col IN (user, version, paynet_id, payment_comprehensive, payment_summary, business_backround,
trade_summary, trade_detail, public_filings, financial_relationships', ucc_filings, transpo_score, 
master_score, legal_name, agri_score, construction_score)
)p

PK is primary key of dbo.phr_audit table

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 12:31:30

I tried just doing this code:
SELECT phra_auditid,PARSENAME(REPLACE(f.Val,'=','.'),2) AS Col,PARSENAME(REPLACE(f.Val,'=','.'),1) AS Val
FROM dbo.phr_audit a
CROSS APPLY ParseValues(a.phra_request_querystring,'&') f

getting:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:36:54

what does below return?


SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'your db name'

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-03-20 : 12:38:04

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources