SQL SERVER – Create a Comma Delimited List Using SELECT Clause From Table Column

I received following question in email :

How to create a comma delimited list using SELECT clause from table column?

Answer is to run following script.

USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
GO

I have previously written similar article where I have demonstrated this method using three sample examples. You can read the article for further information. SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

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

99 thoughts on “SQL SERVER – Create a Comma Delimited List Using SELECT Clause From Table Column

  1. Hi Pinal ,
    There is no need of COALESCE function for this string column with comma sepration. you can do it in this way also without using COALESCE. I did this thing in sql server 2000 and 2005

    USE PUBS
    GO
    DECLARE @Result AS VARCHAR(2000)
    SET @Results = ”

    SELECT @Results = au_lname +’, ‘+ @Results FROM
    authors

    SELECT @Results
    GO
    —————————-And Your Query would be like this—–

    USE AdventureWorks
    GO
    DECLARE @listStr VARCHAR(MAX)
    SET @listStr = ”
    SELECT @listStr = Name + ‘, ‘ + @listStr
    FROM Production.Product
    SELECT @listStr
    GO

    Regards
    Shashi Kant chauhan

    Like

  2. Hai,
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘‘’.

    This error message is displayed when executing this query.

    Like

  3. Hello, Pinal

    I agree with you, COALESCE is a good idea (and a good function too), but in your script just the started value of the variable prevent a resultset NULL, but if any value from the list is null (in this case the “name” field), the entire resultset become NULL too. I suggest the follow solution:

    USE AdventureWorks
    GO
    DECLARE @listStr VARCHAR(MAX)
    SELECT
    @listStr =
    COALESCE ( COALESCE(@listStr+‘,’ ,”) + Name , @listStr)
    FROM Production.Product
    SELECT @listStr
    GO

    Like

  4. I thought these were all good suggestions until I realized it ws only good for one column.

    Is there something similar that would do it for all coluns in a table. For example, I create a temp table with all the data I need to Export then I do something like this:

    Select
    Column_1+’,’+
    Column_2+’,’+
    Column_3+’,’+
    Column_4+’,’+
    Column_5+’,’+
    Column_6+’,’+
    Column_7+’,’+
    Column_8
    From TableToExport

    Any suggestions? Some times I may have 100 or more columsn from various tables.

    Thanks

    Like

  5. Hi ,
    My name is Sushma.
    I am new to learning SQL SQRVER (developer).
    I need some programs links.
    I am new to this so i am faceing lot of problems(even i dont know codding) plz help me.

    Thanks
    Sushma.

    Like

    • –create or replace function getAuthor(i_deptNo VARCHAR)
      — return VARCHAR(4000)
      — is
      — cursor c_emp is
      — select first_Name from employee where id = i_deptNo;
      — v_out VARCHAR2(4000);
      — begin
      — for r_emp in c_emp loop
      — if v_out is null then
      — v_out:=r_emp.first_Name;
      — else
      — v_out:=v_out||’, ‘||r_emp.first_Name;
      — end if;
      — end loop;
      — return v_out;
      — end

      Try it !!!

      Kishor singh

      Like

  6. This is really great. Thank you.

    Is it possible to improve it to include a GROUP BY? I have a table with two columns (ID, Marker). I want to list all of the Markers that exist for each ID as in:

    ID Marker_List
    ————————————–
    1 001, 002
    300 002
    478 008, 985, 212

    Like

  7. hi
    i have a requirement

    *12345678901115100609*22345678901115100609*12355678901115100609*’
    this is string in sqlserver..i want first 10 digits as machine code
    next 4digits as a time and last 6digits as date..”*” symbol is
    neglected

    result like:
    machine time date
    1234567890 1115 100609
    2234567890 1115 100609

    Like

  8. Hi Freinds,

    I have one Query is,
    I have one Table. It has a colum Named RoleID. in this column i have records like ‘3,4,6’. and my query is if i want to select value for ‘4’.
    For Example: In Table I have 5 records. in RoleID column like this

    ID Name RoldeID
    ——————————-
    1 Mr.A 2
    2 Mr.B 2,5
    3 Mr.C 3,4
    4 Mr.D 2,3,5
    5 Mr.E 3,5

    Now I want to select Records where RoleId = 3.
    when i pass select query like this,
    Select * from table where RoleID = 3. then it is not getting any data.

    I m New in SQL please Help.

    Thanks,
    Mazhar

    Like

  9. Hi to All,

    if you add an ORDER BY clause, and if it’s order by a varchar column there is a problem. Example:

    USE AdventureWorks
    GO
    DECLARE @listStr VARCHAR(MAX)
    SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name
    FROM Production.Product
    ORDER BY COLUMN_NAME
    SELECT @listStr
    GO

    If a COLUMN_NAME is a VARCHAR type, result of a query is not comma separated list but a single item. In your example, it would be a single name without a comma. If a COLUMN_NAME is a date or int type, it works just fine. What’s the catch?

    Like

  10. What if you have a (,) within a string, and you just want
    to separate the each side into it’s own column.

    Example:

    12345678,abcdefgh

    How can this be done?

    Like

  11. Hi Vikram,

    You can do this as:

    DECLARE @str VARCHAR(100)
    SELECT @str = ‘12345678,abcdefgh’

    SELECT @str,
    SUBSTRING(@str, 0 , CHARINDEX(‘,’,@str)) AS [1stColunmn],
    SUBSTRING(@str, CHARINDEX(‘,’,@str) + 1, LEN(@str)) AS [2ndColumn]

    Thanks,

    Tejas

    Like

  12. hai

    Could any one help me how to get rid of my problem

    My problem is:
    table name: countryID

    accid localaccid names
    ———————————————–
    101 222 india
    102 333 india
    103 444 india

    201 777 usa
    202 888 usa
    ————————————————

    I need like this

    names localaccid accid
    ————————————————–
    india 222,333,444 101,102,103
    usa 777,888 201,202
    —————————————————

    please how to solve this problem.

    Like

  13. hai

    Could any one help me how to get rid of my problem

    My problem is:
    table name: countryID

    accid localaccid names
    ———————————————–
    101 222 india
    102 333 india
    103 444 india

    201 777 usa
    202 888 usa
    ————————————————

    I need like this

    names localaccid accid
    ————————————————–
    india 222,333,444 101,102,103
    usa 777,888 201,202
    —————————————————

    please how to solve this problem.

    Like

    • It’s easy way.. just follow my instruction.
      Step 1:
      Create Function..

      CREATE FUNCTION [dbo].[udf_UserLocation]
      (@id int)
      RETURNS VARCHAR(500)
      AS
      BEGIN
      declare @loctemp varchar(500)
      SELECT @loctemp=COALESCE(@loctemp + ‘, ‘, ”) + CAST(l.LocName as varchar)
      FROM Location where CountryID=@id
      RETURN (@loctemp)
      END

      Then
      Run Query..

      select CountryName,dbo.udf_UserLocation(id) as ‘Location’ from [Country]

      You will get output as u want without using cursor.

      –dharmik chotaliya

      Like

  14. Hi Pinal,

    I want this in Select distinct query result.

    It is not working in case of distinct

    I have tried like following and i didn’t get desired result

    USE AdventureWorks
    GO
    DECLARE @listStr VARCHAR(MAX)
    SELECT DISTINCT @listStr = COALESCE(@listStr+’,’ ,”) + Name
    FROM Production.Product
    SELECT @listStr
    GO

    Plz give some solution

    Like

    • please try below query

      use AdventureWorks
      Go
      Delcare @listStr VARCHAR(MAX)
      select @listStr =COALESCE(@listStr+’,’ ,”) + ProductData.Name
      from(select distinct Name from Production.Product) as ProductData
      SELECT @listStr
      Go

      Like

  15. Hi Pinal,
    I got the solution for Select Distinct

    but i don’t know about SQL Server 2005 but it is working in SQL Server 2008

    USE AdventureWorks
    GO
    DECLARE @listStr XML
    set @listStr=(SELECT DISTINCT COALESCE( Name,”)+’,’
    FROM Production.Product
    for XML Path(”))

    SELECT SUBSTRING(CAST (@ listStr AS VARCHAR) , 1, LEN(CAST (@ listStr AS VARCHAR))-1)
    GO

    Like

  16. I have two tables (please note that column headers are dynamic – not necessarily 3 or 4 columns..but the same numbr of records will be present in the column data):

    Table 1:

    SortSeqID ColumnHeader
    1 ‘ColA,ColB,ColC’
    2 ‘ColA,ColB,ColC,ColD’
    3 ‘ColA,ColC,ColD’

    Table 2:
    SortSeqID ColumnData
    1 ‘1,2,3’
    2 ’11,13,243,5’
    3 ‘1,5,6’

    Need output like:
    SortSeqID ColA ColB ColC ColD
    1 1 2 3
    2 11 13 243 5
    3 1 5 6

    Like

  17. Hi Pinal,

    Can I use COALESCE in update statement ?

    when I am trying to update the rows to temp table the concatenation doesn’t happen.
    It picks only the first value
    Please help

    Create Table #TempData
    (
    Pe_Logn Varchar(10), Team Varchar(8000)
    )

    Insert Into #TempData
    Select Distinct(PE_DEPT), NULL from iP_Person

    /* Select *from #TempDate */
    Pe_Logn Team
    Finance NULL
    HR NULL
    IT NULL

    /* Getting the people for each department */
    Update #TempData
    Set Team = COALESCE(IsNull(TEAM, ”) + ‘,’,”) + Pe.Pe_Logn from iP_Person Pe
    Where Pe.Pe_Dept = #TempData.Pe_Logn

    /* Select *from #TempData */
    Pe_Logn Team
    Finance ,ABC
    HR ,DEF
    IT ,XYZ

    Actual output should have been
    Pe_Logn Team
    Finance ,ABC, 123, !@#
    HR ,DEF, 234, @#$
    IT ,XYZ, 345, #$%

    Regards,
    Uday

    Like

  18. Hi!

    I have a query like this:

    InvoiceNum Depart.
    ————— ————–
    11000 400
    11000 404
    15000 572

    And I need:

    InvoiceNum Depart.
    ————— ————–
    11000 400, 404
    15000 572

    Is it possible? Thanks!!!!

    Like

  19. If I want this view would COALESCE work? How else can I get this please?

    Data – All in one table
    Col1 Col2
    —— ———-
    USA Denver
    USA Seattle
    USA Chicago
    Canada Vancouver
    Canada Calgary

    Result –

    Col1 Col2
    —– ———–
    USA Denver, Seattle, Chicago
    Canada Vancouver, Calgary

    Like

  20. Hi Pinal,
    I have taken one variable named @Period_List As varchar(max). I have set its vale as
    @Period_List = ‘column1 int, column2 int, column 3′
    Now i am trying to create table using this variable,
    Like,
    CREATE Table #TempTable (@Period_List)
    But it gives error. can you please tell me another way or solution for this problem.

    Like

  21. I want help with sql statment. when I select the data I get is like this

    1 2232 eq233 1000 12-12-2011 11:11 39 9.2 text text

    I want this output to have comma instead of space like

    1, 2232, eq233, 1000, 12-12-2011 11:11, 39, 9.2, text text,

    can some please help.

    Like

  22. Can someone please help me? I have a column of string data collected from a survey. e.g. (Yes; No; Maybe; Don’t know), assigned to a number of respondents. I want to create a list in a seperate COLUMN (retaining a Response heading) for each respondent. i.e.

    Respondent Response
    A Yes
    A No
    A Yes
    B Maybe
    B No
    B Yes

    Like

  23. HOW TO USE SELECT STATEMENTS INSTEAD OF ARGUMENTS IN SPLIT COMMA FUNCTION incase of ms sql 2008 server?it returns value for the below query

    select LISTMEMBER from SysAdmin.fnSplitComma (‘2,3,5′)

    like LISTMEMBER
    2
    3
    5
    but how to get the value when we specifies a column in table which has data in the format 2,3,4 in first row,2,5 in second row.BELOW select statement wont work for that.
    SELECT LISTMEMBER FROM SysAdmin.fnSplitComma(SELECT IMPLanguages FROM CFI.InstructionalMediaPackage WHERE IMPLanguages=p.pkLanguageID) AS
    ‘nameList’
    FROM SysAdmin.M_Lang AS p

    Like

  24. I have a table with FirstName & Last Name as 2 fields, Help me to get all the full name i.e First Name & Last name’s in a single string.
    table1
    ——
    FName LName
    —— ——–
    S Thakur
    P Patel
    R Singh

    Result should be : S Thakur, P Patel, R Singh

    Please help me in this

    Like

    • Hi Priyanka

      Try something like this (it’s very similar to the code in the original code post)

      declare @FullName varchar(Max)
      select
      @FullName = coalesce(@FullName + ‘, ‘,”) + FName + ‘ ‘ + LName
      from table1

      The only difference is you are now coalescing on a concatenated string of the FName and LName

      Hope this helps

      Like

  25. I tried using COALESCE on SQL Server 2008, but it was giving very inconsistent results. When running as a query, the list would look fine, but if I put the same code into a trigger, there would always be a comma prepended to the list.

    I ended up replacing the COALESCE with an equivalent CASE statement which solved the issue:

    @S = (CASE WHEN @S IS NULL THEN ” ELSE @S + ‘,’ END) + @ColValue

    Like

  26. Hi Pinal…
    your articles are very useful…I am an sql server database beginner.
    I read that “we can change environment variables using sp’s ,but can’t with udf’s ” from one of your article -stored procedure vs udf.
    My doubt is:
    How to set environment variables using stored procedures.i did not know how to get those env var values ti t-sql.So,please give me an example to do this.It may be useful for beginners like me..

    Thanks,

    durga

    Like

  27. Hi Pinal…
    your articles are very useful…I am an sql server database beginner.
    I read that “we can change environment variables using sp’s ,but can’t with udf’s ” from one of your article -stored procedure vs udf.
    My doubt is:
    How to set environment variables using stored procedures.i did not know how to get those env var values ti t-sql.So,please give me an example to do this.It may be useful for beginners like me..

    Thanks,

    durga

    Like

  28. Hi Pinal,
    I have a problem trying to resolve this since two days but no use , could you please help me on this

    Source table

    Id Product

    111 BCM123

    111 BCM456

    111 BCM789

    222 BCM123

    222 BCM456

    222 BCM789

    I am looking for the output like this

    Id Product

    111 BCM123,BCM456,BCM789
    222 BCM123,BCM456,BCM789

    Thanks in advance

    Like

  29. Hi all,

    Wanted to know if we can get comma separated list for 1 column and all get rest columns w/o commas in a query having multiple joins?

    Thanks,

    Like

  30. hello Pinal,

    I have a requirement where a variable with comma separated value is passed as a parameter to a SP and those comma separated values are the field names.Here I want to display only the columns mentioned in the input paramenter:

    Ex:-
    Table1 is a table with following columns
    Col1
    Col2
    Col3
    Col4
    Col5
    Col6
    Col7
    Col8

    @Param=’Col1,Col2,Col3,Col5,Col8′ — this is the parameter that wl be input to SP.

    and I want the above mentioned columns to be displayed when the SP is executed.

    Thanks in Advance

    Like

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

  32. how can i display idcount in message box ? i am using Linq to sql and this is my stored procedure.. please replyy……

    ALTER PROCEDURE [dbo].[Transaction]
    (

    @Patient nvarchar(50),
    @E_TO nvarchar(50),
    @R_type int,
    @User_id uniqueidentifier,
    @ReportType nvarchar(50),
    @Patient_no int,
    @Patient_ID_NO numeric(10,0),
    @idcount numeric(18,0)=NULL output
    )
    AS
    BEGIN

    declare @tempid numeric(18,0)
    set @tempid = 0;
    declare @idcnt numeric(18,0)
    select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
    if (@idcnt =0)
    set @tempid=1
    else
    set @tempid = @idcnt +1
    INSERT INTO dbo.Transactions
    (
    Patient,E_TO,R_date,R_from,[User_id],
    report_type,Patient_no,Patient_ID_NO,idcount
    )
    values
    (
    @Patient,@E_TO,getdate(),@R_type,@User_id,@ReportType,@Patient_no,@Patient_ID_NO,@tempid
    )
    End

    Like

  33. Any Suggestions for how I could do this grouping by a header ID. Example: i have 1 invoice with 5 services, I want 1 row with 2 columns, Invoice and list of services. Here is how i am currently pulling this off using a subquery on the table i am selecting from, but i want to make it perform better:

    Stuff(
    (select ‘, ‘ + ServiceTypeWithNotes
    From WisCTE c
    where c.WorkItemID = cte.WorkItemID
    FOR XML Path(”)
    ),1,1,”) as ServiceList

    Like

  34. SELECT *FROM A
    SELECT *FROM C

    SELECT *FROM A
    WHERE NO1 NOT IN
    (
    SELECT NO1 FROM C
    )

    CREATE FUNCTION ITSV2(@A VARCHAR(10))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
    DECLARE @B VARCHAR(10)
    SET @B=”
    SELECT @B=NO1+’,’+@B FROM A
    WHERE NO1 NOT IN
    (SELECT
    NO1
    FROM
    C
    )
    RETURN SUBSTRING(@B

    END

    SELECT dbo.ITSV2(”)

    Like

    • That only returns one column. I am looking for 2, first column being the header rowID the second being list of services for header rowID.

      I have a table with

      Header rowID Service
      1. A
      1. B
      1. C

      I’m looking for
      1. A,B,C

      With out doing a sub query joing on itself

      Select
      RowID
      ,(
      Select stuff(CommaDelimintedserviceList)
      From table1 b
      Where b.rowID = a.rowID
      )
      From table1 as a

      Or is a best way?

      Like

  35. hi pinal;
    i want to store the select clause values into string.if i use one column able to store into string,but more than one column not able to store,please give any suggestion.

    Like

  36. I want to pass my result of in my select query ?? like this
    DECLARE @MaxTRxID as int
    DECLARE @Str as varchar(MAX)
    DECLARE @Str1 varchar(MAX)
    SET @MaxTRxID = 1700
    SELECT @Str = COALESCE(@Str + ‘,’, ”) + ReferenceNo FROM [iC.SI.WC.PosLog.TrxInfo]
    WHERE TrxId > @MaxTRxID
    SET @Str1 = @Str

    select * from [iC.SI.WC.PosLog.CashPayment.LineItem] where ReferenceNo in (@Str1)
    but its not working can anyone help?

    Like

  37. Hello Guys,

    I would appreciate if anyone could help me.
    I am using COALESCE exactly like in the initial example of the post (copied below)

    DECLARE @listStr VARCHAR(MAX)
    SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name
    FROM Production.Product
    SELECT @listStr

    My list is also Varchar(max) but i want to retrieve these values in ascending format using order by?
    Can i do this? and if yes could you please give me a simple example using the above script?
    Also if i cannot do this do you have any other idea how to achieve the same results?

    Thanks in advance!!

    Like

    • This resolved with table variable in a function

      DECLARE @TableVar table (list VARCHAR(max) NOT NULL)

      INSERT INTO @TableVar ( list )
      SELECT Name
      FROM Production.Product
      ORDER BY Name

      DECLARE @listStr VARCHAR(MAX)
      SELECT @listStr = COALESCE(@listStr + ‘ ; ‘ , ”) + list
      from @TableVar

      RETURN @listStr

      Like

  38. Now I have somone likie this:

    create procedure LIST_p(@COLUMN varchar (max), @TABLE varchar (max) )
    AS
    DECLARE @SYNTAX VARCHAR(MAX)

    SET @SYNTAX = ‘DECLARE @listStr VARCHAR(MAX) select @listStr = COALESCE(@listStr+”,” ,””) + ‘ + @COLUMN + ‘ from ‘+ @TABLE +'; select @listStr;’
    execute (@SYNTAX)
    go

    , but I have a question how to execute this procedure using select query

    Like

  39. Hi this is satish bandi

    I want only in singe row from two rows Like

    Sno Name Age
    ”””””|”””””””’|””””’
    1 sa 20
    1 sa 30
    1 sa 40

    Now I want

    Sno Name Age
    ”””””|”””””””’|””””’
    1 sa 20,30,40

    Like

  40. Hi This IS satish

    id name services(I have)
    ———————————–
    1 Joe AA
    1 Joe AB
    1 Joe AC
    2 Judy GH
    2 Judy GC
    3 Kevin AA
    3 Kevin GH

    Result Set:

    id name services (I want)
    ———————————–
    1 Joe AA, AB, AC
    2 Judy GH, GC
    3 Kevin AA, GH

    How can i get like this

    Like

  41. solution to vyshu’s problem

    declare @tt varchar(25) = ”
    ,@ttlen tinyint = 0

    select @tt = ‘ab,bc,cd,de’
    ,@ttlen = LEN(@tt)

    create table #tt(name varchar(10))
    while(@ttlen > 0)
    begin
    insert into #tt
    select LEFT(ltrim(@tt),2)
    select @tt = STUFF(@tt,1,3,”)

    set @ttlen = LEN(ltrim(@tt))
    end
    select * from #tt

    Like

  42. same output i wanted using user defined function , i wanted to pass the ids with comma separed to the function and get the description as per the id.

    ex: my passing dynamic parameters ids should be like this ids = ‘1,4,5,6’ and from function it should return with comma separated description..like san,ta,sri,ssss.

    Thanks
    Santosh

    Like

  43. Hello Pinal,
    I have a problem I have been trying to solve. I have an EDI 834 text file I need to parse into a SQL table using multiple custom delimiters. The file is delimited with a { and member data between different leading identifiers, for example ~DTP{348{D8{20121101~AMT{C1{0 would mean for the month of 2012 10 01 the copay would be $0. The other leading indentifiers would be ~INS – Insured Benefit OR MEMBER LEVEL DETAIL
    ~REF – Reference Information : The REF segment is required to link the dependent(s) to the subscriber.PRIOR COVERAGE MONTHS
    ~DTP – Date or Time or Period
    ~NM1 – Individual or Organizational Name : Either NM1 or N1 will be included depending on whether an individual or organization is being specified. MEMBER EMPLOYER or RESPONSIBLE PERSON
    ~PER – Administrative Communications Contact
    ~N3 – Party Location
    ~N4 – Geographic Location
    ~DMG – Demographic Information
    ~LS – Loop Header
    EC – Employment Class
    ~LS – Loop Header
    ~LX – Transaction Set Line Number
    ~N1 – Party Identification
    ~REF – Reference Information
    ~AMT – Monetary Amount Information
    ~COB – COORDINATION OF BENEFITS
    ~LX – Transaction Set Line Number
    ~N1 – Party Identification
    ~DTP – Date or Time or Period
    ~LE – Loop Trailer
    ~SE – Transaction Set Trailer
    Any help of direction would great.
    thanks

    Like

  44. DECLARE @listStr VARCHAR(MAX)
    SELECT @listStr = COALESCE(@listStr+’,’ ,”) + COALESCE(Name,”)
    FROM Production.Product
    SELECT @listStr

    COALESCE would be required for “Name” too

    Like

  45. How could you create a set of inline comma-separated values for a query? For example, I tried to show orders and include the orderTypes from a cross-reference table:

    SELECT OrderId, Name,
    (
    SELECT COALESCE(Name+’, ‘ ,”) + Name
    FROM OrderType JOIN OrderType_XR ON OrderType_XR.OrderTypeId = OrderType.OrderTypeId WHERE OrderType_XR.OrderId=OrderId
    )
    FROM Order

    but SQL Server reports “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.” Can this be done inline or would I need a SQL function?

    Like

    • Declare @Orders
      SELECT @Orders = COALESCE(@Orders + ‘,’ ,’ ‘ ) + OrderCOL_Name FROM
      (
      SELECT Order FROM OrderTable
      )AS MyCommaOrderedList;

      Like

  46. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

  47. what is alternative to comma separated list in Sql.i have a query suppose i need to store data of some customer as cust_name,cust_id,add,phno,date,items(one by one in separate column or in list ) and price of each item and quantity then the total. how can i design my db as columns here vary dynamically

    Like

  48. hi all,

    i am passing comma separated values to my stored procedure. something like below:

    @id=1,2,3,4
    @firstname=’a’,’b’,’c’,’d’

    now if want something like below:

    id firstname
    1 a
    2 b
    3 c
    4 d

    how can we do this?

    Like

  49. Hi Dears,
    i need this query with where condition
    like below
    USE AdventureWorks
    GO
    DECLARE @listStr VARCHAR(MAX),@parameter varchar(10),@table_name varchar(10)
    set @parameter=’Tag_no=1′
    set @table_name=’name’
    SELECT @listStr = COALESCE(@listStr+’,’ ,”) + @table_name
    FROM online where where + @parameter

    above query show like this
    USE AdventureWorks
    GO
    DECLARE @listStr VARCHAR(MAX),@parameter varchar(10),@table_name varchar(10)
    set @parameter=’Tag_no=1′
    set @table_name=’name’
    SELECT @listStr = COALESCE(@listStr+’,’ ,”) name
    FROM online where where Tag_no
    this query not executing pls any one help me

    Like

  50. Hi pinal, the above given example wont handle null as u commented . For eexample

    create table people
    ( name nvarchar(10)
    )

    insert INTO people VALUES (‘a’),(‘b’),(‘c’),(NULL),(‘d’),(‘e’)
    SELECT * FROM people

    DECLARE @Names VARCHAR(8000)
    SELECT @Names = isnull(@Names+’,’,”)+name FROM people
    SELECT @Names

    The above will result in d,e
    not as expected from a,b,c,d,e

    Like

  51. DECLARE @List VARCHAR(8000)

    SELECT @List = COALESCE(@List + ‘,’, ”) + CAST(LinkedLocationId AS VARCHAR)
    FROM UserMaster
    WHERE LinkedLocationId = 1

    SELECT @List

    — It Gives following error
    /*
    Msg 245, Level 16, State 1, Line 3
    Conversion failed when converting the varchar value ‘27,69,70,1,73’ to data type int.

    */

    Like

  52. I’m trying to make this work in SQL2000 and it won’t work. The column list seems to be dieing at the first “null” valued column

    My procedure is:

    — =============================================
    CREATE procedure [dbo].[CloneRow]

    @tableName varchar(255),

    @keyName varchar(255),

    @oldKeyId int,

    @newTableId int output

    as

    declare @sqlCommand nvarchar(255),

    @columnList varchar(255);

    select @columnList = COALESCE(@columnList+’,’ ,”) + Name

    from syscolumns

    where object_name(syscolumns.id) = @tableName

    and syscolumns.name not in ( @keyName )

    and iscomputed = 0;

    set @sqlCommand = ‘insert into ‘ + @tableName + ‘ ( ‘ + @columnList + ‘) (‘ +

    ‘select ‘ + @columnList + ‘ from ‘ + @tableName + ‘ where ‘ + @keyName + ‘ = @oldKeyId )’

    exec sp_executesql @sqlCommand, N’@oldKeyId int’, @oldKeyId = @oldKeyId

    select @newTableId = @@identity — note scope_identity() won’t work here!
    GO

    Like

  53. Loving your Plural Sight courses, you the man!, now to wrap each list item with single quotes..

    USE AdventureWorks
    GO
    DECLARE @listStr VARCHAR(MAX)
    SELECT @listStr = COALESCE(@listStr+’,’ ,”)+ CHAR(39) + Name+CHAR(39)
    FROM Production.Product
    SELECT @listStr
    GO

    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