Hi everybody i want to ask you about something like the Picture below. How can i split text values and upload them to new datagridview? We use sql server to save datas. And make a record with ','. I can split one row with
textBox5.Text.Split(....)
but we have 2 colums and more then 100 rows. Can u hepl me?
| ID | NUMBER | ERRORS | NOTES | |||||
| 1 | 20 | A10,B20,C34 | ECC,OPP,ERR | |||||
| 2 | 21 | B10,A20 | OPP,ERR | |||||
| 3 | 22 | A22,A12,B21 | ACC,ECC,OPP | |||||
| 4 | 23 | B20 | ACC | |||||
| 5 | 24 | B12,B13,A10 | ECC,ACC,OPP | |||||
|
||||||||
| ID | NUMBER | ERRORS | NOTES | |||||
| 1 | 20 | A10 | ECC | |||||
| 20 | B20 | OPP | ||||||
| 20 | C34 | ERR | ||||||
| 2 | 21 | B10 | OPP | |||||
| 21 | A20 | ERR | ||||||
| 3 | 22 | A22 | ACC | |||||
| 22 | A12 | ECC | ||||||
| 22 | B21 | OPP | ||||||
| 4 | 23 | B20 | ACC | |||||
| 5 | 24 | B12 | ECC | |||||
| 24 | B13 | ACC | ||||||
| 24 | A10 | OPP |
Amitesh VermaPosted Jul 30, 2015, 8:11 AM
create table #temp2 (id int identity(1,1),name varchar(20))
insert into #temp2 values('A10,B20,C34')
insert into #temp2 values('A11,B21,C31')
insert into #temp2 values('A12,B22,C32')
drop table #temp1
create table #temp1 (col1 varchar(3))
DECLARE @Order VARCHAR(MAX) ,@col1 varchar(3),@col2 varchar(3),@col3 varchar(3),@count int
select @count=COUNT(*) from #temp2
while (@count>0)
begin
select @Order=name from #temp2 where id=@count
--select @order
SET @count = @count -1
--select @order
SELECT @col1=PARSENAME(REPLACE(@Order,',','.'),3),
@col2=PARSENAME(REPLACE(@Order,',','.'),2),
@col3=PARSENAME(REPLACE(@Order,',','.'),1)
insert into #temp1 values(@col1)
insert into #temp1 values(@col2)
insert into #temp1 values(@col3)
end
select * from #temp1
harun didinPosted Jul 30, 2015, 9:36 AM
Raja TPosted Jul 30, 2015, 7:43 AM
When get data from SQL server (db) that time replace (\n\r) instead of (,).
Means when you bind the datagridview or reports at time (,)replace the newline (\n).
harun didinPosted Jul 30, 2015, 7:16 AM
Thanks for your reply. But my question is --- i want to split the rows and make a report
for example my first row has A10,B20,C34 and second has ECC,OPP,ERR
Amitesh VermaPosted Jul 30, 2015, 7:06 AM
INSERT INTO #temp2
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
select * from #temp2
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from #temp2
union all
select color, John value, 'John' name
from #temp2
union all
select color, Tim value, 'Tim' name
from #temp2
union all
select color, Eric value, 'Eric' name
from #temp2
) src
group by name