| Author |
Topic |
|
Naveed88
Starting Member
19 Posts |
 Posted - 2009-02-13 : 23:32:49
|
Hi,,,,
i have a function which does parsing string value into table column
ParseStrFromCSV(<parse char>, <csv>)
and it works fine....
now what i want to do is....
i have a huge string in scv, like
@csv = 'name,address,info1,inf2,|name,address,info1,inf2,
|name,address,info1,inf2,|'
and i converted it into a table by '|'
ParseStrFromCSV('|', @csv)
so i get table with 3 rows
name,address,info1,inf2
name,address,info1,inf2
name,address,info1,inf2
now I again want to break all records one by one and it should autometically take
each record and give me
name
address
info1
info2
for each record
so that i can insert these values in other tables |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-13 : 23:46:16
|
declare @str1 varchar(max)
set @str1= 'name,address,info1,inf2'
SELECT
replace(SUBSTRING(@str1,charindex(',',@str1,v.number),abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))),',','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(',' + @str1, v.number, 1) = ','
|
 |
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-02-13 : 23:53:25
|
[code]
declare @reports table(string varchar(600) )
insert into @reports
select 'name,address,info1,inf2'
SELECT
SUBSTRING(s.string, v.Number - 1,
COALESCE(NULLIF(CHARINDEX(',', s.string, v.Number), 0), LEN(s.string) + 1) - v.Number + 1) AS value
FROM @reports AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + s.string, v.Number, 1) = ','
[/code]
Jai Krishna |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
|
|
Naveed88
Starting Member
19 Posts |
Posted - 2009-02-14 : 00:37:26
|
thanks 4 replay
it works But i want result in table
name Address info1 info2
name Address info1 info2
name Address info1 info2
and it gives
name
Address
info1
info2
name
Address
info1
info2name
Address
info1
info2
--
(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer
9867374437-Mumbai.4 |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-14 : 01:10:57
|
declare @str1 varchar(max)
set @str1= 'name,address,info1,inf5,|name,address,info1,inf6,
|name,address,info1,inf2,'
SELECT
replace(SUBSTRING(@str1,charindex('|',@str1,v.number),abs(charindex('|',@str1,charindex('|',@str1,v.number)+1)-charindex('|',@str1,v.number))),'|','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring('|' + @str1, v.number, 1) = '|' |
|
|
|
Naveed88
Starting Member
19 Posts |
Posted - 2009-02-14 : 02:10:06
|
quote:
Originally posted by bklr
declare @str1 varchar(max)
set @str1= 'name,address,info1,inf5,|name,address,info1,inf6,
|name,address,info1,inf2,'
SELECT
replace(SUBSTRING(@str1,charindex('|',@str1,v.number),abs(charindex('|',@str1,charindex('|',@str1,v.number)+1)-charindex('|',@str1,v.number))),'|','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring('|' + @str1, v.number, 1) = '|'
==================
all right let me simplify my question
Now i want column in row format
like
Table1
column
A
B
C
D
i want ...
Table2
col1 col2 col3 vol4 col5
A B C D E
--
(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer
9867374437-Mumbai.4 |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-14 : 02:14:31
|
use dynamic cross tab queries
in 2005 & above u can use pivot |
|
|
|
Naveed88
Starting Member
19 Posts |
Posted - 2009-02-14 : 02:18:37
|
quote:
Originally posted by bklr
use dynamic cross tab queries
in 2005 & above u can use pivot
Thanks for nice suggestion
All Helps are appreciated
--
(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer
9867374437-Mumbai.4 |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-14 : 02:22:01
|
ur welcome
see this it may be helpful to u
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx |
|
|
|
|
|
|
Parse csv 2 times,