Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: forming a sql query

    i have a table[table1] with a field summary
    which has data as
    1-AB-XY
    2-OP-AB
    1-AB-XY
    1-MN-ZZ-AB
    1-OP-AB
    2-AB-XY
    1-MN-ZZ-AB
    1-OP-AB
    1-MN-ZZ-AB


    with the above data hardcoded in the below sample i get the desired result
    (No column name) (No column name)
    1-AB 7
    1-MN 3
    1-OP 2
    1-XY 2
    1-ZZ 3
    2-AB 2
    2-OP 1
    2-XY 1




    /*
    CREATE FUNCTION dbo.split(
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
    ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
    AS
    BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

    INSERT INTO @t(val)
    SELECT r.value('.','varchar(MAX)') as item
    FROM @xml.nodes('/t') as records(r)
    RETURN
    END
    */
    DECLARE @TAB TABLE (SUMMARY VARCHAR(100))

    INSERT INTO @TAB
    SELECT '1-AB-XY'
    UNION ALL SELECT '2-OP-AB'
    UNION ALL SELECT '1-AB-XY'
    UNION ALL SELECT '1-MN-ZZ-AB'
    UNION ALL SELECT '1-OP-AB'
    UNION ALL SELECT '2-AB-XY'
    UNION ALL SELECT '1-MN-ZZ-AB'
    UNION ALL SELECT '1-OP-AB'
    UNION ALL SELECT '1-MN-ZZ-AB'

    SELECT id + '-' + val, count(1)
    FROM (
    SELECT LEFT(SUMMARY, CHARINDEX('-', SUMMARY,1)-1) AS id, val
    FROM @TAB CROSS APPLY dbo.split(SUBSTRING(SUMMARY,CHARINDEX('-', SUMMARY,1)+1 ,100), '-')
    ) TAB
    GROUP BY id + '-' + val



    but instead if i directly need to get the value from my exixting column i dont get the result

    INSERT INTO @TAB
    SELECT SUMMARY from TABLE1


    and how to view data from @TAB???

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by svibuk View Post
    and how to view data from @TAB???
    SELECT SUMMARY from @TAB

  3. #3
    Join Date
    Apr 2012
    Posts
    4

    Invalid length parameter passed to the SUBSTRING function.

    but instead if i directly need to get the value from my exixting column i dont get the error


    Msg 536, Level 16, State 5, Line 2
    Invalid length parameter passed to the SUBSTRING function.


    if i just use the query i get teh aboe error

    SELECT id + '-' + val, count(1)
    FROM (
    SELECT LEFT(SUMMARY, CHARINDEX('-', SUMMARY,1)-1) AS id
    --, val
    FROM TABLE1 CROSS APPLY dbo.split(SUBSTRING(SUMMARY,CHARINDEX('-', SUMMARY,1)+1 ,100), '-')
    ) TABLE1
    GROUP BY id + '-' + val

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •