SQL SERVER – Comma Separated Values (CSV) from Table Column

I use following script very often and I realized that I have never shared this script on this blog before. Creating Comma Separated Values (CSV) from Table Column is a very common task, and we all do this many times a day. Let us see the example that I use frequently and its output.

USE AdventureWorks
GO
-- Check Table Column
SELECT 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

I consider XML as the best solution in terms of code and performance. Further, as I totally prefer this option, I am not even including the linka to my other articles, where I have described other options.


Do you use any other method to resolve this issue? Can you find any significant difference in performance between these options? Please leave your comment here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

110 thoughts on “SQL SERVER – Comma Separated Values (CSV) from Table Column

  1. Could you explain (or point me to a good resource) “FOR XML PATH”?

    I was actually working on a similar query today and wanted to order the select for my CSV query, but got an error indicating you can’t use an ORDER BY clause in a derived table and suggesting the use of FOR XML PATH.

    Thanks!
    Ryan

    Like

  2. I have always wondered why they don’t put a Concatenate function into SQL Server.

    Although the CTE approach might be the most “SQL” method of concatenation, I personally prefer the CLR approach. Here is a pretty good example of this:
    h ttp://msdn.microsoft.com/en-us/library/ms254508%28VS.80%29.aspx

    I also believe that this is a good article discussing the many many ways that people try to concatenate records:
    h ttp://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

    Like

  3. Hi Nitin,
    The 2,200000 is part of the SUBSTRING Function arguments, it says grab the characters starting from 2nd position to position 200000

    Like

  4. We can use stuff fn instead of substring, just to keep the code clean.
    Also, it will be helpful if we want to handle data larger than 200000

    select stuff(query using XML path),1,1,”) AS csv

    Like

  5. The 2, 200000 is ugly as previously pointed out.

    Code that imposes hard coded restrictions like this should not be used as suggested best practices or as teaching statements.

    Like

  6. DECLARE @Csv varchar(Max)
    SELECT @Csv= COALESCE(@Csv + ‘, ‘, ”) +
    CAST(s.Name AS varchar(50))
    FROM HumanResources.Shift s
    ORDER BY s.Name
    SELECT @Csv

    Like

  7. I like your solution but I find that it in many ways is a one off solution and unfortionately will not work if you have multiple columns. Let say you had two tables

    Table A
    ->a_id

    Table B
    ->b_id
    ->name
    ->a_id

    And you wanted the following results
    [a_id],[name,name,name,name]

    If you want to achieve multiple columns you need to create a User Defined Aggregate (see below) so that all you need to do is write a simple query like the one below

    Select a_id, toCSV(name)
    FROM A
    INNER JOIN B on b.a_id = A.a_id

    [Serializable]
    [SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property
    MaxByteSize = 8000) //maximum size in bytes of persisted value
    ]
    public class toCSV : IBinarySerialize
    {
    ///
    /// The variable that holds the intermediate result of the concatenation
    ///
    private StringBuilder intermediateResult;

    ///
    /// Initialize the internal data structures
    ///
    public void Init()
    {
    this.intermediateResult = new StringBuilder();
    }

    ///
    /// Accumulate the next value, not if the value is null
    ///
    ///
    public void Accumulate(SqlString value)
    {
    if (value.IsNull)
    {
    return;
    }

    this.intermediateResult.Append(value.Value).Append(‘,’);
    }

    ///
    /// Merge the partially computed aggregate with this aggregate.
    ///
    ///
    public void Merge(toCSV other)
    {
    this.intermediateResult.Append(other.intermediateResult);
    }

    ///
    /// Called at the end of aggregation, to return the results of the aggregation.
    ///
    ///
    public SqlString Terminate()
    {
    string output = string.Empty;
    //delete the trailing comma, if any
    if (this.intermediateResult != null
    && this.intermediateResult.Length > 0)
    {
    output = this.intermediateResult.ToString(0, this.intermediateResult.Length – 1);
    }

    return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
    intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
    w.Write(this.intermediateResult.ToString());
    }
    }

    Like

  8. hi

    I have a table ‘company’ and it have a column ‘keywords’ where the values are stored like
    KEYWORDS

    Row1 ac, bg, hkl, jh, od
    Row2 gh,jd,kl,ks

    Now what i need is that i want to create a new table name as keywords and it store data like

    KEYWORDS
    Row1 ac
    Row2 bg
    Row3 hkl
    Row4 jh
    …………………………
    ……………………..
    IS it possible to do that??
    please help

    Thanks and regards.

    Like

  9. Hi,

    The same thing can be performed using below function also:

    CREATE FUNCTION [dbo].[Split](@List VARCHAR(6000), @SplitOn VARCHAR(5))
    RETURNS @RtnValue TABLE([Value] VARCHAR(100))
    AS
    BEGIN
    WITH s(start) AS
    (
    SELECT DISTINCT CHARINDEX(‘,’,’,’+@List+’,’,p)
    FROM
    (SELECT number p FROM master..spt_values WHERE type=’p’ and numbers.start)-start-1)
    FROM s WHERE start<len(@List)+2
    )
    INSERT INTO @RtnValue (Value)
    SELECT chunk FROM chunks
    RETURN
    END

    —Use the function Split with your table as below:
    SELECT fSplit.Value AS Result
    FROM CROSS APPLY Split(,’,’) fSplit

    Kind Regards,
    Pinal Dave

    Like

  10. Pingback: SQLAuthority News – Featured on Channel 9 Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – Comma Separated Values (CSV) from Table Column – Part 2 Journey to SQL Authority with Pinal Dave

  12. Hi,

    I have a table like,

    ID | Value
    —- ——–
    1000 1
    1000 2
    1000 3
    1001 2
    1001 3
    1001 4
    1001 5

    I want the output like this

    1000 | 1,2,3
    1001 | 2.3.4.5

    But when i use the below query,

    SELECT ID,(STUFF(select ‘, ‘+value from tblA group by value FOR XML PATH(”)),1,2,”) FROM tblA

    I am getting the output like shown below

    1000 | 1,2,3,4,5
    1001 | 1,2,3,4,5

    Which is wrong.. please help

    Regards

    Like

  13. Hi Taher,

    I found that you applied GROUP BY at wrong place.

    To achieve result as expected you need to change query to:

    DECLARE @tblA TABLE(ID INT, Value INT)

    INSERT INTO @tblA VALUES(1000,1)
    INSERT INTO @tblA VALUES(1000,2)
    INSERT INTO @tblA VALUES(1000,3)
    INSERT INTO @tblA VALUES(1001,2)
    INSERT INTO @tblA VALUES(1001,3)
    INSERT INTO @tblA VALUES(1001,4)
    INSERT INTO @tblA VALUES(1001,5)

    –SELECT * from @tblA

    SELECT ID,
    SUBSTRING(
    (
    select ‘ ,’+ CAST(value AS VARCHAR)
    from @tblA b
    WHERE a.ID = b.ID
    FOR XML PATH(”)
    )
    ,3,100)

    FROM @tblA a
    GROUP BY a.ID

    Let me know if it helps you.

    Thanks,
    Tejas
    SQLYoga.com

    Like

  14. Hello Tejas,

    Thank you very much for your reply. It solved my problem. I was doing group by inside because i wanted to club all similar records (main intention being not to get duplicate records). But now i figured that same thing can be achieved by using DISTINCT.

    Thanks A lot !!!

    Taher

    Like

  15. consider i am having a employee table . iwant my result to be dispalyed like the following.

    emp name reporting order
    steve \steve
    austin \steve\austin
    andrew \steve\austin\andrew
    bruce \steve\austin\andrew\bruce

    thank you in advance.
    Akhiesh

    Like

  16. I got a little giddy when I tried this query on one of the databases I work on. I had used SQL functions to do this in the past… great post! Thanks for sharing.

    Like

  17. How would you remove duplicate comma seperated value from below query (sai,sam,sai)

    USE tempdb;
    GO
    –drop table t1
    CREATE TABLE t1 (id INT, NAME VARCHAR(MAX));
    INSERT t1 values (1,’Jamie’);
    INSERT t1 values (1,’Joe’);
    INSERT t1 values (1,’John’);
    INSERT t1 values (2,’Sai’);
    INSERT t1 values (2,’Sam’);
    INSERT t1 values (2,’Sai’);

    GO

    select
    id,
    stuff((
    select ‘,’ + t.[name]
    from t1 t
    where t.id = t1.id
    order by t.[name]
    for xml path(”)
    ),1,1,”) as name_csv
    from t1
    group by id
    ;

    Like

    • It should be

      select
      t.id,
      stuff((
      select distinct ‘,’ + t1.[name]
      from t1
      where t.id = t1.id
      for xml path(”)
      ),1,1,”) as name_csv
      from t1 as t
      group by t.id
      ;

      Like

  18. Hi Pinal,

    I’ve used the CTE method , Can you please tell me the performance of this approach..

    create table t1
    (
    val varchar(100),
    )

    insert into t1 values (‘A’)
    insert into t1 values (‘B’)
    insert into t1 values (‘C’)
    insert into t1 values (‘D’)
    insert into t1 values (‘E’)

    with CTE (id,val)
    AS
    (
    select id,val from (select Row_number() over (order by val) as id, val from t1) e where id = 1
    union all
    select e1.id, CAST ( CTE.val + ‘,’ + e1.val As varchar(100)) As val from CTE
    inner join (select Row_number() over (order by val) as id, val from t1 ) e1 on e1.id = CTE.id + 1
    )
    select top 1 val from CTE order by id desc

    Like

  19. i am getting an error like Incorrect syntax near ‘XML’.wen i used tat
    SELECT id,
    SUBSTRING(
    (
    select ‘,’ + CAST(value AS VARCHAR)
    from table1 b
    WHERE a.id = b.id
    FOR XML PATH(”)

    )
    ,3,100)

    FROM table1 a
    GROUP BY a.ID

    Like

  20. Hello Tejas,
    i am getting an error like Incorrect syntax near ‘XML’.wen i used tat
    SELECT id,
    SUBSTRING(
    (
    select ‘,’ + CAST(value AS VARCHAR)
    from table1 b
    WHERE a.id = b.id
    FOR XML PATH(“)

    )
    ,3,100)

    FROM table1 a
    GROUP BY a.ID

    Like

  21. Hi Naveen,

    What is SQL SERVER you are using?
    It should work on 2005 and above only.

    XML DataType feature is available from SQL 2005 and above.

    Please let us know. I can run this query at my end on SQL 2005 and SQL 2008.

    Thanks,

    Tejas

    Like

  22. if i don’t have Value for Any Column then its Output Comes As ,,Night as in Your Example Can you Explain How to Apply Case Statement here in Stuff.So that , will not be Come if Value Is not Present for that Column

    Like

  23. To avoid length problems, I have done

    RIGHT((SELECT ',' + [LineString] FROM @firstSort ORDER BY [FirstPoint] DESC FOR XML PATH('')),LEN((SELECT ',' + [LineString] FROM @firstSort ORDER BY [FirstPoint] DESC FOR XML PATH('')))-1)

    I just grab the right side of the string minus 1. That way you don’t accidentally cut anything off.

    Like

  24. HI,

    I have a table named ‘Category_Master’ and it has a column named ‘category’ where the values are stored as follows

    category
    ———–
    a,s,d
    z,x,c
    q,w,e

    when i execute the following query:

    select LEFT(category, CHARINDEX( ‘,’, Artist_Cat) – 1) from Category_Master

    i found output as ….

    category
    ———–
    a
    z
    q

    i.e, I’m getting only first comma separated values as result, but I want the remaining comma separated values in separate table as the result.
    i.e I need the output as :
    category
    ———–
    a
    z
    q

    category
    ———–
    s
    x
    w

    and

    category
    ———–
    d
    c
    e

    Please send me the proper query in order to get the above output as result.

    Thank you in advance
    regards,
    zakir.

    Like

  25. hi

    i have a problem to splite 2 words which is present in one column the first word is in english & the second word is in arabic meaning first & second word are same but in different language so now i want to seperate english word so that i can proceed my work as sql is not understanding arabic character in where condition

    example
    status code
    submitted ارسلت
    valid صحيح
    unsent غيرمرسل

    Like

  26. Hi Dave,

    i am new to this field,recently i gone through one problem..
    that i want to share with u and all readers and require assist for that.
    i have created table with check constraint :

    create table check1
    (id int,
    salary int check(salary >=1000 and salary<=5000));

    inserted value through csv file:
    1,500
    2,1000

    then i have done bulk insert:

    bulk insert check1
    from 'd:\csv.txt'
    with
    (fieldterminator=',',
    rowterminator='\n')

    and out put :

    id | salary
    1 500
    2 1000

    this is wrong output i m geting near id=1 where salary is 500 which is not acording to check constraint
    plz Dave late me know were i m gone wrong

    thanks in advance

    Like

  27. madhivanan, i have one more doubt with above example,
    if table having primary key or other constraints i have to go with same method…

    Like

  28. i m stuck in one problem..which are the new record inserted into the table, i want to know which are the record are inserted..
    emp{e_id,ename,e_dept}

    thanks in advance

    Like

  29. you are amazing sir .
    you always save my day ..

    BTW : i used to do this using a UDF but the performance is terrible

    Declare @result varchar(1000)
    Set @result = ”
    Select @result = @result + ColName+ ‘,’ From myTbl
    Set @Result = Left(@result , Len(@Result) -1)

    thanks a million

    Like

  30. Sir,
    I need to swap 3 rows to 1 columns..

    Input Format :
    ID Columns
    ————————–
    1000 Data – 1
    1000 Data – 2
    1000 Data – 3

    Output Should be…
    ID Column1 Column2 Column3
    ——————————————————
    1000 Data – 1 Data – 2 Data – 3

    Like

  31. i have 3 table NNCE,MAM.ROVE

    NNCE
    ——-
    ID NAME DEPARTMENT
    —————————-
    1 MANI ECE
    2 RAJA ECE
    3 UDHAYANAN ECE
    4 KARTHIK ECE

    MAM
    ID NAME DEPARTMENT
    ————————–
    1 MANI IT
    2 RAJA IT
    3 UDHAYANAN IT
    4 KARTHIL IT

    ROVER
    ——–
    ID NAME DEPARTMENT
    ——————————
    1 MANI MCA
    2 RAJA MCA
    3 UDHAYANAN MCA
    4 KARTHIK MCA

    NOW I WANT OUTPUT

    ID NAME DEPARTMENT
    ———————————-
    1 MANI ECE,IT,MCA
    2 RAJA ECE,IT,MCA
    3 UDHAYANAN ECE,IT,MCA
    4 KARTHIK ECE,IT,MCA

    PLEASE SENT QERRY

    Like

  32. SELECT
    STUFF(
    (
    SELECT
    ‘,’ + cast(Citation_Id as nvarchar(500))
    FROM tollplus.violated_trips
    FOR XML PATH(”)
    ), 1, 1, ”
    ) As CitationId

    here we are getting the column values with CSV, bt i want to get with html tag then..
    plz send me the solution

    Like

  33. SELECT DISTINCT ISSUE_ID,
    STUFF ((SELECT ‘, ‘ + PO_NUMBER + ‘ ( ‘+ CONVERT(VARCHAR(30), QUANTITY_ISSUED) + ‘ ‘ + UOM +’ ) ‘ FROM @temp WHERE CHLD.ISSUE_ID = MAIN.ISSUE_ID FOR XML PATH(”)), 1, 1, ”)AS PO_NUMBER
    FROM @temp AS MAIN

    Like

  34. I have one table. One column ‘Name’ got values like – “1234,abcdef”
    So, I want to display Name values like “abcdef”. Need to avoid “1234,” in value.

    What function we have to use for this. Please let me know the sql query.

    Thanks

    Like

  35. Hello,

    I have to insert csv or excel file into sql server table.my table contains 6 fields but i want to insert only single column values from excel/csv.

    please give me reply.

    Thank you.

    Like

  36. Hi all,
    I need to convert the values in a column which is separated by comma to different column as follows,

    Address Id Phone
    ——————————————————————–
    345 Ram Nagar,Mumbai,MH,425124,India 12345 9945721543
    123 rajaji Nagar,Bangalore,KA,524212,India 67890 8235645678

    convert to this

    Address Street City State Zip Country Id Phone
    ————————————————————————————–
    same as above 345 Ram Nagar Mumbai MH 425124 India 12345 9945721543
    same as above 123 rajaji Nagar Bangalore KA 524212 India 67890 8235645678

    Like

  37. Pinal, can you please help me with this. I know I can go with a stored proc, but for some reasons, I shouldn’t use it.
    allowed_file_extensions in table UPLOAD has comma separated values from table FILE_EXTENSIONS
    if allowed_file_extensions has atleast one entry with comma separated, and that has is_enabled = 0, then it should return 0.
    It will return 1 only if for all values (comma separted) has is_enabled = 1
    FILE_EXTENSIONS
    id description is_enabled
    1 .xls 1
    2 .doc 1
    3 .pdf 1
    4 .rtf 1
    5 .gif 1
    6 .jpeg 1
    7 .jpg 1
    8 .docx 1
    9 .mpeg 0
    10 .mp3 0

    UPLOAD
    upload_id upload_name allowed_file_extensions
    1 Template1 .xls, .doc, .pdf, .mp3, .mpeg, .pptx, .txt
    2 Template2 .xls, .doc, .pdf, .rtf, .mp3, .jpeg, .jpg, .pptx, .txt
    3 Template3 .xls, .doc, .pdf, .rtf, .gif, .pptx, .txt

    Required Output in a view
    upload_id is_enabled allowed_file_extensions
    1 0 .xls, .doc, .pdf, .mp3, .mpeg, .pptx, .txt
    2 0 .xls, .doc, .pdf, .mp3, .jpeg, .jpg, .pptx, .txt
    3 1 .xls, .doc, .pdf, .rtf, .gif, .pptx, .txt

    Like

  38. Thanks Pinal….the above xample which you have mentioned regarding that coma separated values(CSV)….thast amazing and helped a lot

    Regards,
    Yash

    Like

  39. Hi Folks,

    I have a integer field. For an instance i have a value like this 1500000. I want to return the value like this 1,500,000.

    Any idea about this.

    Thank you
    Muthu

    Like

  40. Pinal Dave,

    Thank you for all that you do.

    I was wondering why you don’t like Greeks, Bulgarians, Russians, Chinese, Koreans and Thais?

    Their language gets question marked when I run your T-SQL on the sys.syslanguages table in SQL 2008 R2.

    SELECT langid, name,alias FROM sys.syslanguages

    SELECT @value = SUBSTRING(
    (SELECT ‘,’ + l.name
    FROM sys.syslanguages l
    FOR XML PATH(”)),2,200000)

    us_english,Deutsch,Français,???,Dansk,Español,Italiano,Nederlands,Norsk,Português,Suomi,Svenska,ceština,magyar,polski,româna,hrvatski,slovencina,slovenski,e???????,?????????,???????,Türkçe,British,eesti,latviešu,lietuviu,Português (Brasil),????,???,????,Arabic,???

    Like

    • What is the datatype of @value? It should be nvarchar(max). Also you can simply use

      SELECT SUBSTRING(
      (SELECT ‘,’ + l.name
      FROM sys.syslanguages l
      FOR XML PATH(”)),2,200000) as csv

      Like

  41. For anybody that had the issue of any ampersands being entitized as “& amp;” in your query result – see below for the syntax. That baked my head for a few hours. It uses the xquery .value to convert back to Nvarchar to avoid that issue. Example below Field1 is the key and FieldText is the one I want to summarize as a comma separated value list.

    SELECT DISTINCT tm.Field1

    STUFF(( SELECT DISTINCT ‘, ‘ + [FieldText] as [text()]
    FROM Table1 ts
    WHERE tm.Field1 = ts.Field1
    FOR XML path(”), TYPE
    ).value(N’.’, N’nvarchar(max)’), 1, 1,”) as Field1CSV

    FROM Table1 tm

    Like

  42. I HAVE THE BELOW SCRIPT

    select DISTINCT expiry from Rec_Dtl where grn_number =27964 and m_code =’M605′

    THE RERSUTLS ARE

    exiry
    ——-
    382268; 07/02/05
    976131, 31/05/2011

    I WANT THE SEPRATE TWO VALUES LIKE BELOW.

    DISTINCT EXPIRY 1 , EXPRIY 2
    ————————————-
    382268 07/02/05
    976131 31/05/2011

    I WANT RESULTS EXACTLY LIKE ABOVE. PLEASE HELP ME.

    Like

  43. Pinal Dave,

    I have the following query:

    SELECT
    c.CompanyID,
    c.CompanyName,
    ISNULL(c.LogoFileName,”) AS LogoFileName,
    ISNULL(c.City,”) AS City,
    ISNULL(s.strState,”) as [State],
    ISNULL(m.Mode,”) as Mode
    FROM tblCompany c
    INNER JOIN tblCompanyPartners tcmp on c.CompanyID = tcmp.PartnerCompanyID
    LEFT OUTER JOIN tblStates s ON C.StateID = S.intStateID
    LEFT OUTER JOIN tblCompanyModes cm on c.CompanyID = cm.CompanyID
    LEFT OUTER JOIN tblMode m ON cm.ModeID = m.ModeID WHERE c.PartnerTypeID IN (1,2)
    AND C.CompanyID 31
    and c.CompanyID not in (select PartnerCompanyID from tblCompanyCarrierPartners where [Status] IN (‘Blocked’,’Requested’,’Accepted’) and tblCompanyCarrierPartners.CompanyID = 31) order by companyid desc

    The following result is returned:

    18 christ carrier company file1 San Jose New Hampshire LTL
    18 christ carrier company file1 San Jose New Hampshire Oversize
    14 Milton file2 Dallas California Intermodal

    Now, I need to get the following result:

    18 christ carrier company file1 San Jose New Hampshire LTL, Oversize
    14 Milton file2 Dallas California Intermodal

    I am really stuck up with this task. Any help is appreciated.

    Thanks,
    Varma

    Like

  44. My Table is Like below
    Create table ABC (column nvarchar(2000))

    insert ABC values (‘ab1|ab2|ab3|ab4|ab5|ab6|ab7|ab8′)

    insert ABC values (‘bc1|bc2|bc3|bc4|bc5|bc6|bc7|bc8′)

    here i want only display like below

    ComnnXYZ
    ab7
    bc7

    Like

  45. i have two table named parent and child parent table have PID which is primary key declared as foreign key for child table i want to display results like

    PID ChildID

    1 1,2,3
    2 4,5

    Is it possible for group_concat for two tables in sql 2008 ?

    Like

  46. How to get reverse of it means if we have a variable having comma seprated vaules how to get them into a column so we can have operation on it

    Like

  47. Pingback: SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video « SQL Server Journey with SQL Authority

  48. Pingback: SQL SERVER – Grouping by Multiple Columns to Single Column as A String « SQL Server Journey with SQL Authority

  49. I’m a total SQL newbie, but I found I needed to dump some data from MS SQL to CSV and tried to hack it myself. The frustration with my code and this code is that it doesn’t create proper CSV (e.g. if you have a comma or \n in one of your fields you’re screwed). I wrote a bunch of ugly code to add quotations around each column and replace all of the newlines with spaces. Could your solution incorporate something like that so we could get proper CSV out of MS SQL?

    Like

  50. Hi,
    Its good one to get d value in comma.
    But my query is littel bit different:

    What if I want comma sepereated value for more than two column?
    Agrment Schedule ThirdParty Billing Copmany
    A001000020 1 T001000010 1 ABC
    A001000020 1 T001000017 1 PQR
    A001000020 1 T001000034 1 XYZ

    For the above details i wanna result like

    Agreement Schedule Thirdparty Billing
    A001000020 1 T001000010,T001000017, T001000034 1

    Copmany
    ABC,PQR,XYZ

    Like

  51. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

  52. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  53. Hi, If a column contains coma separated numbers, how to remove a particular number?
    Say column “ID” contains 100,200,300,400,1000,2100,100
    I would like to remove 100
    If I use Replace function, it would modify 1000 and 2100 as well which I don’t want .
    It should remove only 100 which may present at any index of the column ( 100, 200,100)
    How to achieve this?

    Like

  54. In a table with EmployeeName column, I am having employee names in lastname, firstname format & in some rows there are more that 1 employee name seperated by semicolon. How do I convert that column into firstname lastname format.

    Like

  55. i am que this table data one rows one colume Ahmedabad_Bhavnagar,Ahmedabad_Dahod,Ahmedabad_Gandhidham,Ahmedabad_Jamnagar
    how to diff , comm

    Like

  56. I am having value like this

    ID | Value
    —- ——–
    1000 1
    1000 2
    1000 3
    1001 2
    1001 3
    1001 4
    1001 5

    I want the output like this

    1000 | 1
    1000 | 1
    1000 | 1
    1001 | 2
    1001 | 2
    1001 | 2
    1001 | 2
    Please Help..

    Like

  57. Give me stored procedure using sql server,

    Table1:

    Id FullName
    ——————-
    1 Cleo,Smith,james,yanee

    Table2:

    I want to separate the comma delimited string into 3 columns

    Id FullName Name Surname Last DES
    — ———— ——- ———– —— ——
    1 Cleo,Smith Cleo Smith james Yanee

    First table values separate comma vles after that values insert into Table2 ?

    Like

  58. I have a CSV in a col in a table that I needed to flip, I used the following recursive CTE:
    WITH recCTE AS(
    SELECT
    startRead = CONVERT(BIGINT, 1),
    stopRead = CHARINDEX(‘;’, RTRIM(CSVColumn ) + ‘;’ ),
    CSVColumn ,
    ID
    FROM MyTableWithCSVCol
    WHERE CSVColumn IS NOT NULL
    UNION ALL
    SELECT
    startRead = stopRead + 1,
    stopRead = CHARINDEX(‘;’, RTRIM(CSVColumn) + ‘;’, stopRead + 1),
    CSVColumn,
    ID
    FROM recCTE
    WHERE stopRead > 0
    )

    SELECT ID, LTRIM(SUBSTRING(RTRIM(CSVColumn), startRead, stopRead – startRead)) CSVColumnFlipped
    FROM recCTE
    WHERE stopRead > 0 AND startRead stopRead
    ORDER BY ID, startRead

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s