forimg a query with dynamic data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

forimg a query with dynamic data

Author

Topic

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-04-06 : 03:26:25

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 existing column, so replacing TAB with mytablename i get error

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-04-06 : 11:40:16

What values are you using for the SUBSTRING functions?

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 15:31:37

that may be because there are values without - present in your table

what does below return?


select count(*) from table where charindex('-',field)=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-04-07 : 02:21:06

quote:
Originally posted by visakh16

that may be because there are values without - present in your table

what does below return?

select count(*) from table where charindex('-',field)=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

returns 1

(No column name)
1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 18:14:26

so that does mean there's a value without - present and that will break with CHARINDEX

try like


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-04-09 : 06:03:37

quote:
Originally posted by visakh16

so that does mean there's a value without - present and that will break with CHARINDEX

try like

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thanks the query is working in sql but i dont think its helpful for me as i get
cross apply sql construct or statement is not supported

when i am using this query in XSD file

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 11:17:13

hhmm? why do you need to use it inside xsd?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources