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