May 6, 2009 at 6:41 am
Hi, I have table that I am trying to concat a table whare I have multiple services assigned to an ip address example of original
10.1.30.48 3GPM
10.1.30.48 Alcatel Litespan
10.1.30.48 Access7
10.1.30.48 Wandl IPMPLS View DCN
10.1.30.48 WAP GW Nokia
10.1.30.48 8950SP_SA
10.1.30.48 AAA-DCP
10.1.30.48 Actern Line Testing
Result expected is:
10.1.30.48 3GPM,Wandl IPMPLS View DCN,WAP GW Nokia,5620 SAM,8950SP_SA,AAA-DCP,Access7,Acterna Line Testing,Alcatel Litespan
Statment I am using is below, however, when I run the script the result gets truncated to
10.1.30.48 3GPM,Wandl IPMPLS View DCN,WAP GW Nokia,5620 SAM,8950SP_SA,AAA-DCP,Access7,Acterna Line Testing,Alca
Script utilised:
/* Definisco la user-defined function scalare dbo.ufn_ConcatenaServizi */
CREATE FUNCTION dbo.ufn_ConcatenaServizi(@Num1 char(20))
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @servizi varchar(MAX)
SET @servizi = ''
/* Concateno gli utenti separandoli con una virgola */
SELECT @servizi = @servizi + ',' + SERVIZI
FROM [tempdb].dbo.servizi
WHERE NUM1 = @Num1
ORDER BY NUM1
/* Elimino la virgola iniziale */
RETURN STUFF(@servizi, 1, 1, '')
END
GO
/* Query */
SELECT
NUM1, dbo.ufn_ConcatenaServizi(NUM1) AS servizi
FROM [tempdb].dbo.servizi
GROUP BY NUM1
Please can any one help me
Thanks in advance
Des
May 6, 2009 at 12:41 pm
Hi
Just tried and works on my system:
DECLARE @t TABLE (Ip VARCHAR(20), Txt VARCHAR(30))
INSERT INTO @t
SELECT '10.1.30.48', '3GPM'
UNION ALL SELECT '10.1.30.48', 'Alcatel Litespan'
UNION ALL SELECT '10.1.30.48', 'Access7'
UNION ALL SELECT '10.1.30.48', 'Wandl IPMPLS View DCN'
UNION ALL SELECT '10.1.30.48', 'WAP GW Nokia'
UNION ALL SELECT '10.1.30.48', '8950SP_SA'
UNION ALL SELECT '10.1.30.48', 'AAA-DCP'
UNION ALL SELECT '10.1.30.48', 'Actern Line Testing'
DECLARE @ret VARCHAR(MAX)
SELECT @ret = ''
SELECT @ret = @ret + ',' + Txt
FROM @t
PRINT STUFF(@ret, 1, 1, '')
Greets
Flo
May 7, 2009 at 2:13 am
Flo,
Thanks for the answer, the problem I have is that I have multiple IP's over 10000 and multiple services, not all IP's have the same services. I do not know what all the IP's are or what all the services are, hence was using concat and holdkey.
Any help would be most apriciated.
Thanks
May 7, 2009 at 2:23 am
Hi
Do you mean something like this?
DECLARE @t TABLE (Ip VARCHAR(20), Txt VARCHAR(30))
INSERT INTO @t
SELECT '10.1.30.48', '3GPM'
UNION ALL SELECT '10.1.30.48', 'Alcatel Litespan'
UNION ALL SELECT '10.1.30.48', 'Access7'
UNION ALL SELECT '10.1.30.48', 'Wandl IPMPLS View DCN'
UNION ALL SELECT '10.1.30.48', 'WAP GW Nokia'
UNION ALL SELECT '10.1.30.48', '8950SP_SA'
UNION ALL SELECT '10.1.30.48', 'AAA-DCP'
UNION ALL SELECT '10.1.30.48', 'Actern Line Testing'
UNION ALL SELECT '10.1.30.49', 'AAA-DCP'
UNION ALL SELECT '10.1.30.49', 'Actern Line Testing'
SELECT
Ip,
STUFF(
(
SELECT ',' + Txt
FROM @t
WHERE Ip = t.Ip
FOR XML PATH(''))
, 1, 1, '')
FROM @t t
GROUP BY Ip
Greets
Flo
May 7, 2009 at 2:41 am
Could this be a problem with your client settings? SSMS and Query Analyzer have a setting called "Maximum number of characters displayed". You find it under Tools>Options>Query Results.
The default value for results in text is 256. If the column value is longer itis not really truncated, only not displayed.
[font="Verdana"]Markus Bohse[/font]
May 7, 2009 at 3:31 am
Markus,
I have checked the setting & changed it 8000 and it still truncates.
May 7, 2009 at 10:43 am
Flo,
More or less, the code you sent assumes that I know the IP addresses and Services, however this is not the case, is there a way that I can use a variable or wildcard es. n+1 in the union statement until it does not find any more records.
Thanks in Advance
Des
May 7, 2009 at 10:49 am
nilkanth.desai (5/7/2009)
Flo,More or less, the code you sent assumes that I know the IP addresses and Services, however this is not the case, is there a way that I can use a variable or wildcard es. n+1 in the union statement until it does not find any more records.
Thanks in Advance
Des
How? I added several new IP addresses and services and Flo's code handled them just fine. Nothing in the code, as far as i can tell, is dependent on an external value.
May 7, 2009 at 10:51 am
I got it! The table variable with the test data, that is what you are talking about. No, you use the code Flo provided, but change it to use your own table. The table variable is just there to show you how the code works.
May 7, 2009 at 11:03 am
Lyn,
I understand that if i manually enter the ip addresses and services the code handles this, as I said it assumes I know all the IP addresses AND services, this is not the case, the source database that I used in the original code using concat has almost 1 million records. The reason I need to do this is so that I can populate another table that the contact point use when called by the end user they can by typing in the ip address get a line list of services assigned to that IP.
May 7, 2009 at 11:12 am
No one's suggesting that you enter IP addresses manually. The Table variable that Lynn and Flo had in there code was a substitite for the real table. To implement their solutions, you would ignore the table variable and it's population, then, in the query, use the name of your table instead of the name of the table variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2009 at 11:20 am
nilkanth.desai (5/7/2009)
Lyn,I understand that if i manually enter the ip addresses and services the code handles this, as I said it assumes I know all the IP addresses AND services, this is not the case, the source database that I used in the original code using concat has almost 1 million records. The reason I need to do this is so that I can populate another table that the contact point use when called by the end user they can by typing in the ip address get a line list of services assigned to that IP.
First, my name is Lynn, not Lyn.
Second, I figured out what you were confused about as shown in my very next post (quoted here)
I got it! The table variable with the test data, that is what you are talking about. No, you use the code Flo provided, but change it to use your own table. The table variable is just there to show you how the code works.
May 7, 2009 at 11:35 am
Sorry Lynn.
I understand that I change the table variable, however the result I get is:
10.1.8.0 3GPM,3GPM,3GPM,3GPM,3GPM,3GPM………………………………………….3GPM,
The code I am using if I understand correctly is as follows:
USE tempdb
SELECT
NUM1,
STUFF(
(
SELECT ',' + SERVIZI
FROM holddups
WHERE NUM1 = holddups.NUM1
FOR XML PATH(''))
, 1, 1, '')
FROM holddups
GROUP BY NUM1
Thanks in Advance
Des
May 7, 2009 at 1:11 pm
Perhaps try adding 'DISTINCT' to get rid of some of the redundancy? I did not test it; it's a thought.
May 7, 2009 at 1:30 pm
Query your data for this just this IP: 10.1.8.0. See if there are duplicate services. If so, you either need to add a distinct or determine if there are additional criteria you need to apply to solve the problem.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply