Sachin

I have a sting '101|1|1|2|3|4|5|6|7|8|9|10##102|1|1|2|3|4|5|6|7|8|9|10' want to split in row column values like split with '##' for rows and | for columns

Output
id val1 val2 val3 val4 val5 val6 val7 val8 val9 val10
101 1 2 3 4 5 6 7 8 9 10
102 1 2 3 4 5 6 7 8 9 10

DataBase Q

Q Get Table result with Total and SubTotal.
CC         1    40
DD         1    5
EE         2    35
SubTOTAL   :::  35
SubTOTAL   :::  45
TOTAL      :::  80
      ------------------ Or ---------------------
with ct as  
( select pname, pvalue ,ggroup from Ta1 
 union all 
 select 'Sub Total',SUM(cast(pvalue as int)),'-' from Ta1 group by ggroup  
union all 
select pname, pvalue ,ggroup from Ta1 where ggroup = 2 
union all 
select 'Grand TOTAL' as Name,SUM(cast(pvalue as int)) as pvalue,'-' as ggroup from Ta1 )
 select


Q: select the duplicate records from table
select* from ta1 where ggroup in(select ggroup from Ta1 group by ggroup having count(*) > 1) pname, pvalue,ggroup from ct where ggroup is not null and pvalue is not null     ------------------ Or ---------------------
select
         case when pname = (select top 1 pname from Ta1 where ggroup = O.ggroup order by pname desc)         then (select cast(sum(cast(pvalue as int)) as char(10)) from Ta1
where pname <= O.pname and ggroup = O.ggroup)    else ' ' end as 'Sub Total',             case when pname = (select top 1 pname from Ta1 order by ggroup desc)             then (select sum(cast(pvalue as int)) from Ta1)
    else ' ' end as 'Grand Total'  from Ta1 O where pvalue is not null order by pname O.pname,O.pvalue, pname,ggroup,pvalue from CTE where pvalue is not null 'TOTAL' as Name,':::' as ggroup,SUM(cast(pvalue as int)) as pvalue from Ta1 'SubTOTAL' as Name,':::' as ggroup,SUM(cast(pvalue as int)) as pvalue from Ta1 group by ggroup pname,ggroup,pvalue from Ta1 pvalue is not null  group by pname with rollup order by pname desc Ta1   isnull(convert(VARCHAR,pname),'GRAND TOTAL') AS ggroup,  sum(cast(pvalue as int)) AS 'Total In Inventory'
;
Q Get value  by ','
-- Check Table ColumnSELECT Name    FROM HumanResources.Shift
GO-- Get CSV values  SELECT SUBSTRING(
         (SELECT ',' + s.Name      FROM HumanResources.Shift s      ORDER BY s.Name     FOR XML PATH('')),2,200000) AS CSV
GO

Q find the 3rd highest value from table
 selectcast(pvalue as int)  from ta1 e1  where 2= ( count(distinct pvalue) from ta1 where cast(pvalue as int) > cast(e1.pvalue as int)) )
--------------OR---------------
SELECTTOP 1 pvalue    FROM     (
      SELECT distinct TOP 3             cast(pvalue as int) as pvalue         FROM ta1 group by cast(pvalue as int)         ORDER BY cast(pvalue as int
        TmpTable         ) DESC )     ORDER BY cast(pvalue as int) ASC
--------------OR---------------
 select max(id)as id
       from student
    where id in
  (select top 3 id from student1 )    select count(distinct pvalue) from ta1    where    cast(pvalue as int) > cast(e1.pvalue as int)