Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 12th, 2008, 01:04 PM
Authorized User
 
Join Date: May 2007
Posts: 95
Thanks: 5
Thanked 0 Times in 0 Posts
Default retrieving comma separated values from column

Hi All

I am a novice in SQL Server 2005,got stuck in retreiving record.
Here is the problem:-->

I have one table say TABLE1, Three columns are there:

-------------------------------------------------------------------------------

REASON | REASON_TYPE | Empid
-------------------------------------------------------------------------------

pay,career,brand | primary,secondary,primary | 109

brand, culture | primary,secondary | 453


for first row here (pay corresponds to primary, career corresponds to secondary,brand corresponds to primary)
similary for the second column....
similarly many records.........
So u can see, in both columns values are separted as commas,
in the stored procedure , i m passing parameter @reason.

I have two drop downs in my design page( i m using C# as a language and Visual studio 2005 as a tool)

Dropdowns are 'primary reasons' and 'secondary reasons'. Both the dropdowns have values which is there in REASON column.
So, now if the user selects 'pay' from the dropdown 'primary reasons' and click on submit button, then the stored procedure should check from the table,in the REASON Column,'pay' is there or not, if the first value is pay,then it should correspondingly check the REASON_TYPE column, the first value is primary. then that empid should get retrieved.

Its quite complex..., but i have tried my best to put it in a simple manner. Please help me by giving Sql query or stored procedure to retrieve record.







 
Old April 12th, 2008, 11:39 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Unfortunately SQL server doesn't have good string parsing functionality as it's not intended to be used that way. You'll need to refer to a SQL reference and do some manual manipulation of the strings.

If there is any opportunity to restructure the database I'd recommend it. As you are finding, working with data in single column values is a pain, it's not optimal and not normalized. You would be far better off breaking apart the individual bits of data into separate columns or rows and working with the data as a set.

-Peter
peterlanoie.blog
 
Old April 13th, 2008, 01:47 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Ok then... before I get to the solution of this problem, let me give some friendly advice...

First, abhishekkashyap27, you spent a bit of time formatting what the data looked like... if you'd spend just a little more time to format it like this...
Code:
 CREATE TABLE Table1
        (
         Reason VARCHAR(50),
         Reason_Type VARCHAR(50),
         EmpID INT
        )

 INSERT INTO Table1
        (Reason,Reason_Type,EmpID)
 SELECT 'pay,career,brand','primary,secondary,primary',109 UNION ALL
 SELECT 'brand,culture','primary,secondary',453
... maybe you get better answers than someone telling you to normalize your data before you went any further. Shoot, they might even write some code to show you how. Spread the word, you get better answers when you make it easier on the people trying to help you. ;) If you tell 8 people and they tell 8 people... etc, etc, the entire country should know the proper way to post data in less than 2 days.

Second, Peter, you told abhishekkashyap27 to normalize the data... what if abhishekkashyap27 has a thousand rows of such data? How is (s)he going to normalize it? It would be real nice if you showed some code as to how to do that.

Ok... sorry about the soapbox, but I had to get both of those things off my chest.

Using the data provided, splitting the data is no big thing... matching the data is no big thing... the key is like Peter said... the data has to be normalized to use it for this problem. This is how to do it... READ the comments for what it's doing...
Code:
--=====================================================================================
--      Create the test data provided by the OP. This is NOT part of the solution.
--=====================================================================================
 CREATE TABLE Table1
        (
         Reason VARCHAR(50),
         Reason_Type VARCHAR(50),
         EmpID INT
        )

 INSERT INTO Table1
        (Reason,Reason_Type,EmpID)
 SELECT 'pay,career,brand','primary,secondary,primary',109 UNION ALL
 SELECT 'brand,culture','primary,secondary',453

--=====================================================================================
--      Normalize the data for usage.  This could be turned into a view, I suppose.
--=====================================================================================
;WITH 
cteReason AS
(--==== Split the REASON and include the EmpID and the position the data was in
     -- as an "ElementNumber" so we can rejoin with the correct REASON_TYPE.
 SELECT ROW_NUMBER() OVER (PARTITION BY EmpID   ORDER BY t.Number  ) AS ElementNumber,
        EmpID,
        Reason = SUBSTRING(','+s.Reason, t.Number+1, CHARINDEX(',', s.Reason+',', t.Number+1)-t.Number)
   FROM Master.dbo.spt_Values t
  RIGHT OUTER JOIN --Necessary in case SomeCsv is NULL
        dbo.Table1 s 
     ON SUBSTRING(','+s.Reason, t.Number, 1) = ',' 
    AND t.Number BETWEEN 1 AND LEN(','+s.Reason)
    AND t.Type = 'P'  --Identifies rows with numbers from 0 to 2047
)
,
cteReason_Type AS
(--==== Split the REASON_TYPE and include the EmpID and the position the data was in
     -- as an "ElementNumber" so we can rejoin with the correct REASON.
 SELECT ROW_NUMBER() OVER (PARTITION BY EmpID   ORDER BY t.Number  ) AS ElementNumber,
        EmpID,
        Reason_Type = SUBSTRING(','+s.Reason_Type, t.Number+1, CHARINDEX(',', s.Reason_Type+',', t.Number+1)-t.Number)
   FROM Master.dbo.spt_Values t
  RIGHT OUTER JOIN --Necessary in case SomeCsv is NULL
        dbo.Table1 s 
     ON SUBSTRING(','+s.Reason_Type, t.Number, 1) = ',' 
    AND t.Number BETWEEN 1 AND LEN(','+s.Reason_Type)
    AND t.Type = 'P'  --Identifies rows with numbers from 0 to 2047
)
--===== Join each REASON with its corresponding REASON_TYPE
 SELECT r.EmpID, r.ElementNumber,r.Reason, rt.Reason_Type
   FROM cteReason r
  INNER JOIN
        cteReason_Type rt
     ON r.EmpID = rt.EmpID
    AND r.ElementNumber = rt.ElementNumber
The output from that should be just what you need. Like I said, you might even try converting it to a view although Peter's suggestion of using it to normalize your data would be a better one.


--Jeff Moden
 
Old April 14th, 2008, 11:40 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Any feedback on this?

--Jeff Moden
 
Old April 15th, 2008, 02:17 AM
Authorized User
 
Join Date: May 2007
Posts: 95
Thanks: 5
Thanked 0 Times in 0 Posts
Default

Thanks for the help...., this worked....:)

-- Abhishek

 
Old April 15th, 2008, 06:55 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Cool... that's what I wanted to know... Thank you for the feedback.

--Jeff Moden
 
Old April 21st, 2008, 09:27 AM
Authorized User
 
Join Date: May 2007
Posts: 95
Thanks: 5
Thanked 0 Times in 0 Posts
Default

Hi Jeff

Just for curiousity, i tried to filter out using patindex but there is a problem.
If i am selecting pay as a primary reason, i am getting it but i am getting other unwanted records too.

here is the stored procedure:

USE [StudyProject]
GO
/****** Object: StoredProcedure [dbo].[HR_SP_EXIT_CONS_RPT_GETDTLS] Script Date: 04/18/2008 16:41:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Abhishek
-- Create date: 10/04/2008
-- Description: Retrieve Details
-- =============================================
-- exec [HR_SP_EXIT_CONS_RPT_GETDTLS] 'R','','','',3,'','','','','',0

ALTER PROCEDURE [dbo].[HR_SP_EXIT_CONS_RPT_GETDTLS](@strMode char(1),@strLoc varchar(20),
                                                @strVert varchar(20),@strDept varchar(30),@IntLevel numeric(2),
                                                @strRating varchar(15),@strPrRsn varchar(50),@strScRsn varchar(50),
                                                @FromDate varchar(10),@ToDate varchar(10),@IntLoS numeric(9))

AS
If(@strMode = 'R')

    declare @Empno varchar(10)
    declare @Name varchar(40)
    declare @FC varchar(20)
    declare @Lev varchar(10)
    declare @Dept varchar(40)
    declare @Reas varchar(1000)
    declare @ReasType varchar(1000)
    declare @Loc varchar(10)
    declare @LWD varchar(10)
    --declare @Rating varchar(15)
    declare @strSql nvarchar(4000);

begin
             CREATE TABLE #TEMPTABLE(empno VarChar(10), Name VarChar(40),FC varchar(20),
             Lev varchar(10),LWD varchar(10),
             Dept varchar(40), Reas varchar(1000),
             ReasType varchar(1000),Loc varchar(10))--,Rating varchar(15))


set @strPrRsn='%' + @strPrRsn + '%'
print 'rea code: ' + @strPrRsn
  --if(@strPrRsn<>'' or @strScRsn <>'')
    -- begin




            --if @strPrRsn<>'Select'
              if @strPrRsn<>'All'
                begin
declare ExitCons Cursor
           for
                       Select empno,Name,function_code,Convert(varchar,last_work ing_dt,103),
                      (select desg_level from desg_master where desggrade = A.Desggrade)Level,
                       --(select Rating from Hris.dbo.hike_per_master)Rating,
                      (select DEPT_DESC from dept_mast where DEPT_CODE = A.DEPT_CODE)Department,
                       REASON,REASON_TYPE,
                      (Select loc_desc from location_mast where loc_code in(select location_code from employee_master where empno =assc_id))Location
                       from employee_master A,EXIT_CONSOLIDATION B
                       where A.empno=assc_id
                           AND
                       REASON LIKE @strPrRsn order by assc_id
                      --order by REASON,REASON_TYPE --'%pay%'--
                end
            else
                begin
declare ExitCons Cursor
           for
                       Select empno,Name,function_code,Convert(varchar,last_work ing_dt,103),
                      (select desg_level from desg_master where desggrade = A.Desggrade)Level,
                       --(select Rating from Hris.dbo.hike_per_master)Rating,
                      (select DEPT_DESC from dept_mast where DEPT_CODE = A.DEPT_CODE)Department,
                       REASON,REASON_TYPE,
                      (Select loc_desc from location_mast where loc_code in(select location_code from employee_master where empno =assc_id))Location
                       from employee_master A,EXIT_CONSOLIDATION B
                       where A.empno=assc_id order by assc_id
                       -- order by REASON,REASON_TYPE
                       -- AND
                      -- REASON LIKE @strPrRsn --'%pay%'--
                end

            open ExitCons
            fetch next from ExitCons into @Empno,@Name,@FC,@LWD,@Lev,@Dept,@Reas,@ReasType,@ Loc--,@Rating
            while (@@fetch_status=0)
                Begin
                        if(patindex('%,%',@Reas)>0 and @Reas<>'')
                           begin
                                 if(patindex('%,%',@Reas)=patindex('%,%',@ReasType) AND ltrim(rtrim(SUBSTRING(@ReasType, patindex('%,%',@Reas)+1, 1)))='P')
                                    begin

                                        --if(@REAS <> ',' or @REAS <> ',,')
                                            --begin
                                                INSERT INTO #TEMPTABLE(Empno,Name,FC,LWD,LEV,DEPT,REAS,REASTYP E,LOC)--,Rating)
                                                values(
                                                @EMPNO,@NAME,@FC,@LWD,@LEV,@DEPT,@REAS,@ReasType,@ Loc)--,@Rating)
                                            --end
                                  end
                                 if(patindex('%,%',@Reas)=patindex('%,%',@ReasType) AND ltrim(rtrim(SUBSTRING(@ReasType, patindex('%,%',@Reas)+1, 1)))='S')
                                    begin

                                        --if(@REAS <> ',' or @REAS <> ',,')
                                            -- begin
                                                INSERT INTO #TEMPTABLE(Empno,Name,FC,LWD,LEV,DEPT,REAS,REASTYP E,LOC)--,Rating)
                                                values(
                                                 @EMPNO,@NAME,@FC,@LWD,@LEV,@DEPT,@REAS,@ReasType,@ Loc)--,@Rating)
                                            --end
                                    end
                             end
                            else
                            begin
                                if(@REAS <> ',' or @REAS <> ',,')
                                    begin
                                        INSERT INTO #TEMPTABLE(Empno,Name,FC,LWD,LEV,DEPT,REAS,REASTYP E,LOC)--,Rating)
                                                values(
                                                 @EMPNO,@NAME,@FC,@LWD,@LEV,@DEPT,@REAS,@ReasType,@ Loc)--,@Rating)
                                    end
                            end


            fetch next from ExitCons into @Empno,@Name,@FC,@LWD,@Lev,@Dept,@Reas,@ReasType,@ Loc--,@Rating

            END
            close ExitCons
            deallocate ExitCons

    --end




        set @strSql = 'select * from #TEMPTABLE WHERE 2=2'
        --set @strSql = @strSql + 'and location_code='''+@strLoc+''''+
                    --and function_code='''+@strVert+''' +' and DEPT_CODE='''+@strDept+''''

        if(@strLoc <>'')
            begin
                set @strSql = @strSql +' and Loc='''+@strLoc+''''
            end

        if(@strVert <>'')
            begin
                set @strSql = @strSql +' and FC='''+@strVert+''''
            end

        if(@strDept <>'')
           begin
            set @strSql = @strSql +' and Dept='''+@strDept+''''
           end

-- if(@strRating <>'')
-- begin
-- set @strSql = @strSql +' and Rating='''+@strRating+''''
-- end
--

        if(@FromDate <>'')
             begin
                      SET @strSql = @strSql + ' AND LWD >= ''' +@FromDate+''''

             end
         if(@ToDate <>'')
            begin
                     SET @strSql = @strSql + ' AND LWD <= ''' +@ToDate+''''
            end


            --print @strLevel

        if(@IntLevel <>0)
            begin
                set @strSql = @strSql +' and cast(Lev as char(2))='+Cast(@IntLevel as char(2))+''
            end



print (@strsql)
exec (@strsql)
drop table #TEMPTABLE

END


I think something i m missing something after this statement:
if(patindex('%,%',@Reas)>0 and @Reas<>'')
 begin

 Can u just look through it .....

-- Abhishek












Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a list box from comma separated values CoderNH Access VBA 3 October 20th, 2008 08:37 AM
Comma Separated values aldwinenriquez SQL Server 2005 3 June 1st, 2007 08:52 PM
comma separated list MunishBhatia SQL Server 2000 11 March 21st, 2007 03:56 PM
Comma Separated Column Value thru T-SQL subhendude SQL Server 2000 2 December 12th, 2006 01:42 PM
Select from Comma Separated Values sasidhar79 SQL Server 2000 2 March 28th, 2005 06:58 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.