October 1, 2017 at 12:47 pm
All,
I think it's correct to provide a table structure for any questions so I'll include that first:
/****** Object: Table [dbo].[tabusrContactType] Script Date: 01/10/2017 19:42:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tabusrContactType](
[uniqueref] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Title] [varchar](255) NOT NULL,
[Active] [bit] NULL,
[HotTopic] [bit] NULL,
[HTReport] [bit] NULL,
[ContactTypeGrpRef] [uniqueidentifier] NULL,
CONSTRAINT [PK_tabusrContactType] PRIMARY KEY CLUSTERED
(
[uniqueref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabusrContactType] ADD CONSTRAINT [DF_tabusrContactType_uniqueref] DEFAULT (newid()) FOR [uniqueref]
GO
ALTER TABLE [dbo].[tabusrContactType] ADD CONSTRAINT [DF_tabusrContactType_Active] DEFAULT ((-1)) FOR [Active]
GO
ALTER TABLE [dbo].[tabusrContactType] ADD CONSTRAINT [DF_tabusrContactType_HotTopic] DEFAULT (0) FOR [HotTopic]
GO
ALTER TABLE [dbo].[tabusrContactType] ADD CONSTRAINT [DF_tabusrContactType_HTReport] DEFAULT (0) FOR [HTReport]
GO
If I run the following:
DECLARE @SQLStr NVARCHAR(max) declare @query AS NVARCHAR(MAX)
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + [a].[Column]
FROM (SELECT '[' + title +']' AS [Column] FROM tabusrcontacttype where active=1) AS a
order by [column]
select @Sqlstr
I get the following correct result:[Account Questions],[App Issue],[Website query - Customer error]
If I take out the where active=1 then I get the result:
[Website query - Customer error]
Can anyone advise why removing the 'where active=1' removes several columns from the output?
If I run SELECT '[' + title +']' AS [Column] FROM tabusrcontacttype where active=1 on it's own then I get all the columns as I would expect.
Thanks
October 2, 2017 at 3:01 am
You've provided DDL, which is great, however, no DLM, so all these queries return NULL. We can't access your data so we can't replicate your problem. Can you please provide DLM for your table?
For the moment, a complete and utter guess:
When filtering your results, no active customers have the values [Account Questions] or [App Issue] in the column [column] (that is a awful name to call a column!). Thus, the only result to come back is "[Website query - Customer error]". Coalesce is working exactly as intended.
Self referencing variables are n't the best thing in the world to use either. Personally, I would use STUFF and FOR XML PATH. Again, without DLM, this is a guess, however, this might bring back the same result, but is a "better" solution.SELECT STUFF((SELECT ',[' + Title + ']'
FROM tabusrContactType
WHERE Active = 1
FOR XML PATH('')),1,1,0) AS SQLStr;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 9:53 am
>> I think it's correct to provide a table structure for any questions so I'll include that first: <<
yes, this has been basic netiquette for the 30+ years I’ve been posting on SQL forums. Newbies, like you, are usually not this polite and assume that everybody can read their minds to get the DDL they need. However, after that, pretty much everything you posted is wrong.
The first thing is that unique identifiers, GUID, UUID, etc. were never meant to be used inside a schema. The G stands for global, the use stands for universal, etc. they refer to things that are not local to the schema. They can never be a valid key insight a schema, by definition.
The bit data type is highly proprietary and doesn’t behave the way you think it does. At one point Microsoft switched it from what IT people think of as a bit to a numeric data type. That means it’s nullable, and not just limited to zeros and ones. Your design does not take this into consideration at all! You should have written:
active_flg BIT DEFAULT 0 NOT NULL,
hot_topic_flg BIT DEFAULT 0 NOT NULL,
ht_report_flg BIT DEFAULT 0 NOT NULL,
The “_FLG” postfix is what ANSI ISO 11179 and data modeling call an attribute property. It’s also never used in a valid, well designed RDBMS schema. That was assembly language and essentially you are writing assembly language flags in a high level language. You never really learned to think in SQL at all.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/bit-of-a-problem/
The rest of your code is pretty much what a non-SQL, assembly language programmer would have written 20 years ago. Build a string, then execute it. You embed metadata in data element names. In fact, the “TAB_” affix is so bad that it actually has a name; we call it a Tibble and make jokes about it.
You use oversized default strings because you have no control over your data. Using “A” as an alias shows me that you’re still thinking in terms of alphabetically named tape drives, and not in terms of a valid relational logical model. You don’t seem to know that “column” is never a valid data element; it’s a description of a logical storage method used in the schema. Did you mean “column_name” instead? The final error is building a CSV list in SQL. Dr. Codd would probably throw up on you 🙁 All of RDBMS is based on First Normal Form (1NF).
>> I get the following correct result: <<
did you ever hear the joke about the teacher asking students “what is 4×6?”
Mary answers “Red!”
Billy answers “Thursday!”
Sammy answers “24!”
The teacher then asks, “Sammy tell Mary and Billy how you got your answer.”
Billy answers “I divided Red by Thursday, of course!”
you got a totally nonrelational result. It happened contain the information you are after. You’re going to think you did it right, and you’re going to keep screwing up like this for the rest of your career and let you actually take the time to learn to do it right. If you want to drop into another language completely, like XML or whatever, then you can get something like this and it’ll be valid.
>> Can anyone advise why removing the 'where active_flg=1' removes several columns from the output? <<
Since we have no sample data, valid specs, or valid DDL, I’m going to guess something screwed up in your data. Since your data model screwed up, this is quite likely
.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 2, 2017 at 9:59 am
At the OP: Feel free to ignore Joe's ramblings, although, his comment of no Sample Data is still just as important as when I stated it. 🙂 We need that sample data to replicate your behaviour, and without it we can only guess at why your results are not what you expected.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 10:55 am
Thom A,
Thank you for your help and apologises for not including the Sample Data. The below should be the required info:
/* ==Scripting Parameters==
Source Server Version : SQL Server 2016 (13.0.4001)
Source Database Engine Edition : Microsoft SQL Server Standard Edition
Source Database Engine Type : Standalone SQL Server
Target Server Version : SQL Server 2016
Target Database Engine Edition : Microsoft SQL Server Standard Edition
Target Database Engine Type : Standalone SQL Server
*/
CREATE TABLE [dbo].[tabusrContactType](
[uniqueref] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Title] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Active] [bit] NULL,
[HotTopic] [bit] NULL,
[HTReport] [bit] NULL,
[ContactTypeGrpRef] [uniqueidentifier] NULL,
CONSTRAINT [PK_tabusrContactType] PRIMARY KEY CLUSTERED
(
[uniqueref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'c03df907-b51f-40ed-b58d-0408dd94da74', N'Damaged in transit', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'10e06f82-1d6e-400b-a6b2-0fc6a21b0c96', N'Software Issue', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'0e82235c-5762-4aff-8b03-159017d0d99c', N'Chasing Order', 0, 0, 0, N'57ca5da1-6a91-4c5b-a402-8b9ed7121e2a')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'8439af98-0490-4513-af0a-1617bf9f0076', N'Voucher Box Position', 1, 0, 0, NULL)
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'63ed4cbc-c990-4cc6-ab9d-1e61c9dc1f4d', N'Price Query', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'9225523d-468c-4057-b0a2-28740a92c1e7', N'Promotion query - Customer error', 1, 0, 0, N'c4cb8ce5-b4c4-482f-9ede-979f18ceeb8e')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'88a24587-59b9-47fe-8f8a-296fb48dab69', N'Chasing Order Dispatched Late', 1, 0, 0, N'd1f97425-907e-437c-ab3b-7599920c0436')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'6fa22ff7-62f4-41a2-b999-37c14667d66e', N'Quality - Customer', 1, 0, 0, N'168a7b6e-e8b0-4730-834d-35da1a805327')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'8bbb5cc8-b094-4ea2-9082-385cb4ce6085', N'Website query - Customer error', 1, 0, 0, N'8e00d475-ef88-4669-9470-88ffebff137b')
GO
INSERT
[dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'1be0c3c2-313e-449a-858b-42f9e9223f99', N'Damaged Order', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'5c0b4bdf-e35a-4423-84bb-4bee30100dda', N'Envelopes', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'2a710b41-34a5-46e3-aa13-51982c2b140f', N'Missed Collection', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'73d0241d-e780-4a56-8aa3-5247df39235f', N'Returned Mail', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'af8bbd0b-a776-435d-bdbb-75ed8025230d', N'Other', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'029aeb71-6088-48ad-b1a0-7d6372f40c79', N'Chasing Order In turnaround', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'fe26e52d-14ba-458f-bd4a-8843f3e7efbe', N'Not a service we offer', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'1765826b-34b7-476a-ba0b-91f997b1101f', N'Screen crash/freeze', 0, 1, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'15949459-945b-4cad-aadc-9ac6752071e9', N'Software query - Customer error', 1, 0, 0, N'a8cdfaa5-047e-418e-afdf-75c67242f171')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'4b3aa572-598e-4876-8791-9af278cfcc7b', N'How Do I? Query', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'e9234897-1013-4d86-81d7-9d767b421236', N'Delivery Query', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'2ac8d628-5a0a-4293-9f2d-aa616c6a4fa2', N'Promotion Issue', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'304f9cdf-1957-45ec-9641-aa783de458ef', N'Late Delivery', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'ffd9f330-e9db-4dcc-a516-bd461fd08044', N'AIC', 0, 0, 0, N'a8cdfaa5-047e-418e-afdf-75c67242f171')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'4a1926b0-6554-4b8e-abd6-bf12d81793f6', N'Account Questions', 1, 0, 0, N'8e00d475-ef88-4669-9470-88ffebff137b')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'689c6d44-2a99-481a-9ac1-c3bb532dc8aa', N'Website Issue', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'8ed83d00-c5f0-4811-b2df-d142ebe8f84f', N'Chasing Order Not Arrived', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'c8141dfa-3264-4604-8869-d4d2281e2ace', N'Pricing Issue', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'e8854a47-7862-48dd-a29a-df378ae900e6', N'Damaged in lab', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'5b9297f5-c542-4d18-9cb8-eb5cef327dc9', N'Kiosk Complaint', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'2a8a31d7-d9bd-4919-9f30-ed115ede8137', N'Product Query', 1, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'24005e4c-2d1f-49ce-85d2-eec9ae75273a', N'Software bug', 1, 0, 0, N'6d32ee52-398f-41f9-b15c-61855d101a81')
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'716420db-0fab-4a29-becd-f66413a0d6df', N'App Issue', 1, 0, 0, NULL)
GO
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'fb6719a3-1458-4a5b-9f48-fbb1a9232381', N'Re-order Tool Problem', 0, 0, 0, N'f71ea5f0-d3b1-4f29-92a9-576516b762e2')
GO
ALTER TABLE [dbo].[tabusrContactType] ADD CONSTRAINT [DF_tabusrContactType_uniqueref] DEFAULT (newid()) FOR [uniqueref]
GO
ALTER TABLE [dbo].[tabusrContactType] ADD CONSTRAINT [DF_tabusrContactType_Active] DEFAULT ((-1)) FOR [Active]
GO
ALTER TABLE [dbo].[tabusrContactType] ADD CONSTRAINT [DF_tabusrContactType_HotTopic] DEFAULT (0) FOR [HotTopic]
GO
ALTER TABLE [dbo].[tabusrContactType] ADD CONSTRAINT [DF_tabusrContactType_HTReport] DEFAULT (0) FOR [HTReport]
GO
You are absolutely correct 'column' is a horrible name. To be honest that part was sample code and I hadn't changed it while I was trying to find the issue with the missing results so as not to change more than one thing at once.
The XML path code your provided (thank you) seems to work. it puts a zero at the front. I will have a look at XML path and see if I can teach myself why. Apologises I've just moved from SQL 2000 to 2016 so have a lot of new functions to learn.
if posting questions such as my original one fits into the "You should be able to fix this yourself and not ask on a professional forum" then I would understand that.
Thanks
October 2, 2017 at 11:02 am
This appears to be a bug in SQL Server (or a feature?). It's easily corrected if you remove the derived table and use a direct query.
DECLARE @SQLStr NVARCHAR(max) declare @query AS NVARCHAR(MAX)
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + '[' + title +']'
FROM tabusrcontacttype
order by Title;
select @Sqlstr
I would still encourage you to use the method posted by Thom to concatenate rows.
By the way, your DEFAULT constraint for Active is incorrect as it's defined as -1 but that would be converted into 1. All values different to 0 (zero) or 'false' are converted to 1 on bit columns.
October 2, 2017 at 11:07 am
as1981 - Monday, October 2, 2017 10:55 AMThe XML path code your provided (thank you) seems to work. it puts a zero at the front. I will have a look at XML path and see if I can teach myself why. Apologises I've just moved from SQL 2000 to 2016 so have a lot of new functions to learn.if posting questions such as my original one fits into the "You should be able to fix this yourself and not ask on a professional forum" then I would understand that.
Thanks
To remove the zero, you need to understand how the STUFF function works. If you don't understand it after reading the documentation, come back and ask, don't be shy.
For more information on the concatenation method, read this: Creating a comma-separated list (SQL Spackle) - SQLServerCentral
There's no such thing as "You should be able to fix this yourself and not ask on a professional forum". We all started somewhere and all we ask is willingness to learn and if possible to pay it forward.
October 2, 2017 at 12:11 pm
Ha, I see why... /Facepalm. I blame not getting a weekend!
Like Luis said, I think it'll be really good to fix this yourself, you'll learn something new. If you do struggle, let us know. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 12:20 pm
Thom, Luis,
Thanks for all your comments and help. I will take a look and see if I can figure it out myself. I'll post an update but I might not get chance for a bit.
I know the lack of weekend problem 🙂
Thanks
Andrew
October 2, 2017 at 1:33 pm
Hello,
I think I figured it out (posted to help others rather than to 'show off'). Stuff has worked as follows:
It's taken the result returned from the select statement as it's input (first parameter).
It's started at the first character (second parameter) - The first character is a leading comma due to the comma before the [ in 'SELECT ',[' + Title + ']'')
It's taken the number of characters specified in the third parameter and replaced it by the fourth parameter
SELECT STUFF((SELECT ',[' + Title + ']'
FROM tabusrContactType
WHERE Active = 1
FOR XML PATH('')),1 ,1,0) AS SQLStr;
So by replacing the fourth parameter (
SELECT STUFF((SELECT ',[' + Title + ']'
FROM tabusrContactType
WHERE Active = 1
FOR XML PATH('')),1 ,1,'') AS SQLStr;
)
The zero gets removed as Thom intended.
If this is correct then this reference might be useful to anyone wanting to find more information on the command https://docs.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql
Thanks
October 2, 2017 at 1:47 pm
as1981 - Monday, October 2, 2017 1:33 PMHello,
I think I figured it out (posted to help others rather than to 'show off'). Stuff has worked as follows:
It's taken the result returned from the select statement as it's input (first parameter).
It's started at the first character (second parameter) - The first character is a leading comma due to the comma before the [ in'SELECT ',[' + Title + ']'')
It's taken the number of characters specified in the third parameter and replaced it by the fourth parameter
SELECT STUFF((SELECT ',[' + Title + ']'
FROM tabusrContactType
WHERE Active = 1
FOR XML PATH('')),1 ,1,0) AS SQLStr;So by replacing the fourth parameter (
SELECT STUFF((SELECT ',[' + Title + ']'
FROM tabusrContactType
WHERE Active = 1
FOR XML PATH('')),1 ,1,'') AS SQLStr;)
The zero gets removed as Thom intended.
If this is correct then this reference might be useful to anyone wanting to find more information on the command https://docs.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql
Thanks
That's exactly right! I'd, for some reason, put a 0 instead of ''. I blame the long weekend (working). :p
Well done on resolving. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 3, 2017 at 5:42 am
Excellent. Then, while you're still in learning mode ;-), please go on and hone your new tools to perfection using this example:SELECT STUFF(
(
SELECT N',' + QUOTENAME(Title) as [text()]
FROM tabusrContactType
WHERE Active = 1
ORDER BY Title
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1 ,1,'') AS SQLStr;
To give you a hint why you need these additions, here's an extra line to add one more row to your test data:INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'132D4AE2-64D9-4DE6-A876-161B0D6247CD', N'My [love] & <hate> for special''s', 1, 0, 0, NULL)
GO
October 3, 2017 at 6:01 am
R.P.Rozema - Tuesday, October 3, 2017 5:42 AMExcellent. Then, while you're still in learning mode ;-), please go on and hone your new tools to perfection using this example:SELECT STUFF((
SELECT N',' + QUOTENAME(Title,'[]')
FROM tabusrContactType
WHERE Active = 1
ORDER BY Title
FOR XML PATH(''), TYPE).VALUE('./text()[1]','nvarchar(max)'),1 ,1,'') AS SQLStr;To give you a hint why you need these additions, here's an extra line to add one more row to your test data:
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'132D4AE2-64D9-4DE6-A876-161B0D6247CD', N'My [love] & <hate> for special''s', 1, 0, 0, NULL)
GO
Test the small tweak I included in your code for an added boost on performance.
October 3, 2017 at 6:17 am
Thanks! That's one for me to take away 🙂
October 5, 2017 at 2:41 pm
Luis Cazares - Tuesday, October 3, 2017 6:01 AMR.P.Rozema - Tuesday, October 3, 2017 5:42 AMExcellent. Then, while you're still in learning mode ;-), please go on and hone your new tools to perfection using this example:SELECT STUFF((
SELECT N',' + QUOTENAME(Title,'[]')
FROM tabusrContactType
WHERE Active = 1
ORDER BY Title
FOR XML PATH(''), TYPE).VALUE('./text()[1]','nvarchar(max)'),1 ,1,'') AS SQLStr;To give you a hint why you need these additions, here's an extra line to add one more row to your test data:
INSERT [dbo].[tabusrContactType] ([uniqueref], [Title], [Active], [HotTopic], [HTReport], [ContactTypeGrpRef]) VALUES (N'132D4AE2-64D9-4DE6-A876-161B0D6247CD', N'My [love] & <hate> for special''s', 1, 0, 0, NULL)
GOTest the small tweak I included in your code for an added boost on performance.
Sorry I've been busy with some other issues.
I can't work this one out. I'm thinking the following might be something near?:
The quotename adds the square brackets to contain the square brackets in the line you added.
.value makes the whole field be interpreted as one string rather than get split up by the XML path
The /text()[1] tells the system it's text and that the first value is required so it doesn't have to calculate that for itself?
Thanks
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply