forimg a sql query using split

  • 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

  • OK so we have sample data and desired output but I don't understand what the output represents. Can you explain what you are trying to do here?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • What does this mean:

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

    I am slightly confused.

  • in my sql table i have a column summary with 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

    i need to get the count of individual category with the id

    i mean in row having data as

    1-AB-XY

    first it shld be split as 1-AB & 1-XY

    same way for all the rows of that column

    then i need to find the total for each category as below

    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

    which mean 1-AB = 7 . 1- AB count is 7

    instead of the above sample data if i use

    SELECT id + '-' + val, count(1)

    FROM (

    SELECT LEFT(SUMMARY, CHARINDEX('-', SUMMARY,1)-1) AS id, val

    FROM MYTABLE CROSS APPLY dbo.split(SUBSTRING(SUMMARY,CHARINDEX('-', SUMMARY,1)+1 ,100), '-')

    ) MYTABLE

    GROUP BY id + '-' + val

    i get the substring error

  • Neither one of your statements "I don't get the result" nor "i get the substring error" do really help us to narrow down the issue. SQL Server is kind enough to usually provide pretty specific error messages.

    Why do you hide that information and obfuscate the message instead?

    My pot shot would be there are values in the summary column that don't have the dash at all.

    You could test it with

    SELECT *

    FROM MYTABLE

    WHERE CHARINDEX('-', SUMMARY,1) = 0

    If that's not the case, please post the error message as returned by SQL Server.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • ssurekha2000 (4/7/2012)


    in my sql table i have a column summary with 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

    i need to get the count of individual category with the id

    i mean in row having data as

    1-AB-XY

    first it shld be split as 1-AB & 1-XY

    same way for all the rows of that column

    then i need to find the total for each category as below

    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

    which mean 1-AB = 7 . 1- AB count is 7

    instead of the above sample data if i use

    SELECT id + '-' + val, count(1)

    FROM (

    SELECT LEFT(SUMMARY, CHARINDEX('-', SUMMARY,1)-1) AS id, val

    FROM MYTABLE CROSS APPLY dbo.split(SUBSTRING(SUMMARY,CHARINDEX('-', SUMMARY,1)+1 ,100), '-')

    ) MYTABLE

    GROUP BY id + '-' + val

    i get the substring error

    Run the following query on your real table (you may have to change the table name) and let us know what the result is.

    SELECT COUNT(*)

    FROM MyTable

    WHERE CHARINDEX('-',Summary) = 0

    ;

    And, when you ask for help, always post the full error message you're getting so people don't have to ask you a thousand questions about what the error actually is. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, I'm feeling today is one of those days where I'm just getting ignored... 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Dang. Sorry Lutz. My bad. I had opened this thread last night and forgot to "refresh" before posting so I never saw your post until just now.

    At least it shows that more than one person knows what the problem is. 😀

    Speaking of that particular problem, this should do the trick as well as adding a little easily removable "bling".

    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'

    UNION ALL SELECT '3'

    ;

    WITH

    cteSplit AS

    (

    SELECT ID = SUBSTRING(data.Summary,1,ISNULL(NULLIF(CHARINDEX('-',data.Summary)-1,-1),100)),

    split.ItemNumber,

    Item = CASE WHEN split.ItemNumber > 1 THEN split.Item ELSE NULL END,

    ItemCount = COUNT(*) OVER (PARTITION BY data.Summary)

    FROM @tab data

    CROSS APPLY dbo.DelimitedSplit8K(data.Summary,'-') split

    )

    SELECT ID = ISNULL(ID,''),

    Item = CASE WHEN GROUPING(ID) = 1 THEN 'Total'

    WHEN GROUPING(Item) = 1 THEN 'SubTotal'

    ELSE ISNULL(Item,'** No Items **')

    END,

    ItemCount = COUNT(Item)

    FROM cteSplit

    WHERE (Item > '' AND ItemCount > 1)

    OR (Item IS NULL AND ItemCount = 1)

    GROUP BY ID,Item WITH ROLLUP

    OPTION (MAXDOP 1)

    ;

    Results:

    ID Item ItemCount

    1 AB 7

    1 MN 3

    1 OP 2

    1 XY 2

    1 ZZ 3

    1 SubTotal 17

    2 AB 2

    2 OP 1

    2 XY 1

    2 SubTotal 4

    3 ** No Items ** 0

    3 SubTotal 0

    Total 21

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @ssurekha2000,

    I used a different splitter than you did. Please see the following article for a splitter that's a wee bit more efficient than the XML splitter you used (code attached near the end of the article in the resources section)

    http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply