April 6, 2012 at 4:24 am
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
April 6, 2012 at 8:30 am
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/
April 6, 2012 at 9:09 am
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.
April 7, 2012 at 1:07 am
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
April 7, 2012 at 2:59 am
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.
April 7, 2012 at 11:31 am
ssurekha2000 (4/7/2012)
in my sql table i have a column summary with data as1-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
Change is inevitable... Change for the better is not.
April 7, 2012 at 12:24 pm
Jeff, I'm feeling today is one of those days where I'm just getting ignored... 😉
April 7, 2012 at 12:35 pm
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
Change is inevitable... Change for the better is not.
April 7, 2012 at 12:52 pm
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply