-1

I have two SQL Server tables, EmployeeMst and EmployeeHistory

EmployeeMst is my master table, and EmployeeHistory is my record table.

I store comma-separated values in EmployeeHistory

Now I want to get employee names with use of a T-SQL query

My table EmployeeMst

EmpNo   EmpName
---------------------
1       abc
2       xyz
3       apple
4       banan
5       apk
6       ico
7       exe
8       botle
9       dev
10      tbl
11      col
12      fun
13      dbo
14      xnb
15      png
16      jpgn
17      pngns
18      dknd
19      dnckd
20      dnicnd

EmployeeHistory:

EmpYear    EmpNo
---------------------------------
2010       1,2,3,10,11,12,13
2011       1,2,3,4,5,6,16,17,18

Now i want output as below

OUTPUT:

EmpYear  EmpNo   EmpName
2010      1       abc
2010      2       xyz
2010      3       apple
2010      10      tbl
2010      11      col
2010      12      fun
2010      13      dbo
2011      1       abc
2011      2       xyz
2011      3       apple
2011      4       banan
2011      5       apk
2011      6       ico
2011      16      jpgn
2011      17      pngns
2011      18      dknd

There are so many comma-separated values in EmpNo column

SQLFIDDLE

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hiren gardhariya
  • 1,247
  • 10
  • 29
  • i understand, but i can't get output from other question. – Hiren gardhariya Nov 26 '14 at 09:38
  • You can if you put the output of the function into a temp/variable table and then use that in your final select – Bernd Linde Nov 26 '14 at 09:39
  • http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns – johny kumar Nov 26 '14 at 09:42
  • i am not able to do this. if you know than please share – Hiren gardhariya Nov 26 '14 at 09:42
  • What are you having problems with? Have you looked at the multiple different answers showing different ways of doing it? Go writing the code and then come back with a specific issue that you have, not with a "write this code for me" – Bernd Linde Nov 26 '14 at 09:43
  • if you know the answer than put this query in my sqlfiddle. – Hiren gardhariya Nov 26 '14 at 09:45
  • 2
    That is not what SO is for, we don't write code for others, we show you what you could do and let you figure it out and by that learn how to. If you want code written, pay someone for it – Bernd Linde Nov 26 '14 at 09:48
  • Gosh - when are programmers going to **stop** violating even the **First Normal Form** of database design by stuffing everything into **comma-separated** column values?!?!?!?! This is the devil's work ..... – marc_s Nov 26 '14 at 09:48
  • Gosh - when are programmer's going to **stop** violating even the **First Normal Form** of database design by stuffing everything into **comma-separated** column values?!?!?!?! This is the devil's work ..... – marc_s Nov 26 '14 at 09:49
  • i know this is devil's work. but this work is not done by me. it's my senior's work. and now i handle this old project. – Hiren gardhariya Nov 26 '14 at 09:51
  • Mr. marc_s, i know this question is duplicate but no answer is perfect as per my output. – Hiren gardhariya Nov 26 '14 at 09:59
  • Try this method: http://sqlfiddle.com/#!6/6b3c9/4 – Vasily Nov 27 '14 at 04:35
  • @ Vasily, thanks for positive answer. i got solution another way you also try Select EmployeeHistory.EmpYear,EmployeeMst.EmpNo,EmployeeMst.EmpName from EmployeeHistory Inner Join EmployeeMst On CharIndex(',' + EmployeeMst.EmpNo + ',', ',' + EmployeeHistory.EmpNo + ',') > 0 – Hiren gardhariya Nov 27 '14 at 05:46

1 Answers1

0

Try this code:

 CREATE FUNCTION [dbo].[SplitString]  
( 
    -- Add the parameters for the function here 
    @myString nvarchar(max), 
    @deliminator nvarchar(10) 
) 
RETURNS  
@ReturnTable TABLE  
( 
    -- Add the column definitions for the TABLE variable here 
    [part] [nvarchar](max) NULL 
) 
AS 
BEGIN 
        Declare @iSpaces int 
        Declare @part nvarchar(max) 

        --initialize spaces 
        Select @iSpaces = charindex(@deliminator,@myString,0) 
        While @iSpaces > 0 

        Begin 
            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0)) 

            Insert Into @ReturnTable(part) 
            Select @part 

    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0)) 


            Select @iSpaces = charindex(@deliminator,@myString,0) 
        end 

        If len(@myString) > 0 
            Insert Into @ReturnTable 
            Select @myString 

    RETURN  
END

then 

select part from  SplitString('a,b,c,d,e,f,g',',')
Sankar M
  • 4,549
  • 12
  • 37
  • 55