July 15, 2020 at 2:06 pm
Hi,
relatively new to SQL development so hoping this isnt a silly question but here goes...
If you have to output 1 record per line into a text file with each column separated by a delimiter but the last column must have multiple values what's the best way to achieve? For example, Joe Bloggs has ordered Top Gear magazine starting in July but wants the back issues for this year so there would be 1 record with his address details and the last column would say he requires issues 1,2,3,4,5 and 6. I could output 6 records but only want the 1, guessing it would be grouping in some form
Thanks in Advance
July 15, 2020 at 3:04 pm
Use STRING_AGG.
John
July 15, 2020 at 3:11 pm
STRING_AGG()?
USE AdventureWorks2017;
GO
SELECT OrganizationLevel
, STRING_AGG(CONVERT(nvarchar(max), JobTitle), CHAR(13)) AS csv
FROM HumanResources.Employee
GROUP BY OrganizationLevel;
July 15, 2020 at 3:16 pm
I think this really depends.
My first step would be to determine what output format is required by the end user. If they require it to be a comma separated list of issues, then that is what you should give them. I would not worry about what you want, but what they require.
Once you know that, it depends on your data. But I think the STUFF command along with FOR XML will be your friend here. See - https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql
Here is a quick little query I did up that demonstrates this, but without knowing your data, I don't know how accurate it will be:
DECLARE @tmp TABLE
(
[cust] VARCHAR(255)
, [magazine] VARCHAR(255)
, [issue] INT
);
INSERT INTO @tmp
(
[cust]
, [magazine]
, [issue]
)
VALUES
(
'Joe'-- cust - varchar(255)
, 'top gear'-- magazine - varchar(255)
, 1-- issue - int
)
, (
'Joe'-- cust - varchar(255)
, 'top gear'-- magazine - varchar(255)
, 2-- issue - int
)
, (
'Joe'-- cust - varchar(255)
, 'top gear'-- magazine - varchar(255)
, 3-- issue - int
)
, (
'Joe'-- cust - varchar(255)
, 'top gear'-- magazine - varchar(255)
, 4-- issue - int
)
, (
'Joe'-- cust - varchar(255)
, 'top gears' -- magazine - varchar(255)
, 5-- issue - int
)
, (
'Joel'-- cust - varchar(255)
, 'top gear'-- magazine - varchar(255)
, 6-- issue - int
);
SELECT
[customers].[cust]
, [customers].[magazine]
, STUFF((
SELECT
',' + CAST([issues].[issue] AS VARCHAR(MAX))
FROM@tmp AS [issues]
WHERE[issues].[cust]= [customers].[cust]
AND [issues].[magazine] = [customers].[magazine]
FOR XML PATH('')
)
, 1
, 1
, ''
) AS [issues]
FROM@tmp AS [customers]
GROUP BY[customers].[cust]
, [customers].[magazine];
I changed some data to give you an example of how to handle multiple customers and magazines, but that should get you on the right track.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 15, 2020 at 3:21 pm
thanks everyone for the replies, it's much appreciated. I will have a play
July 15, 2020 at 8:43 pm
>> relatively new to SQL development so hoping this isnt a silly question but here goes... <<
No, it's not silly, but it is based on a lack of experience and education. I strongly recommend you get a good book on RDBMS and get the basics. First of all rows in the table are nothing like records. Rows have constraints, data types dollars, references, can be actual or virtual, etc. in fact in SQL you will find that 80 to 90% of your work will be done in the DDL and not in the DML. This is the nature of a declarative language. Your mindset is still stuck hopelessly in procedural languages. "Before you can drink new tea, you must empty the old tea from your cup." -- Zen proverb.
>>If you have to output 1 record [sic] per line into a text file with each column separated by a delimiter but the last column must have multiple values what's the best way to achieve? <<
The best way is to never, never, never do this. When you get around to reading that book on RDBMS, you'll learn about a thing called first normal form (1NF), this is the foundation of the relational model. It says that every column in a table must be a scalar value or NULL. You cannot have any kind of repeated group or structured value.
>> For example, Joe Bloggs has ordered Top Gear magazine starting in July but wants the back issues for this year so there would be 1 record [sic] with his address details, and the last column would say he requires issues 1,2,3,4,5 and 6. I could output 6 records [sic] no but only want the 1, guessing it would be grouping in some form <<
Another fundamental mistake! The table represents a set of entities or a relationship. But you got a COBOL style record, which contains both the subscriber and the magazines in one structure. From your narrative, try something like this:
CREATE TABLE Subscribers
(subscriber_id CHAR(10) NOT NULL PRIMARY KEY,
subscriber_name VARCHAR (35) NOT NULL,
subscriber_street_address VARCHAR (35) NOT NULL,
etc);
CREATE TABLE Subscriptions
(issn CHAR(10) NOT NULL, -- do you know this code?
subscriber_id CHAR(10) NOT NULL
REFERENCES Subscribers(subscriber_id),
PRIMARY KEY (issn, subscriber_id),
initial_issue_nbr INTEGER NOT NULL CHECK(initial_issue_nbr > 0),
final_issue_nbr INTEGER NOT NULL
CHECK(initial_issue_nbr <= final_issue_nbr),
etc);
I'm obviously leaving out some stuff about making sure that renewed subscriptions don't overlap with existing subscriptions, back issues have been delivered, etc. but I think you see my point about separating things into the relationship and the entities involved.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 15, 2020 at 9:05 pm
I agree with jcelko here 100%.
If this table structure is not pre-defined for you, I would definitely look at doing it in at least 1st normal form. You will also want to figure out what data you want to capture before you start creating things. Like you probably want to capture the subscribers name, address, phone number (optional), email address (optional), etc. And you will also want to be able to map up what magazine they have subscribed to, a start and end date for the subscription, if the issue has been mailed out already and if so on what date, etc.
Now, if you inherited the system that is already set up for you, which I am sure most if not all DBA's have been hit with at some point, your options are to either make due with what you have or slowly work on getting it into a nicer, normalized format.
But as jcelko pointed out - your question is not a silly one. There are very few silly questions out there. Without asking questions, nobody would learn anything. And sometimes it is the seemingly "silly" questions that really make people think twice about why they do things a certain way.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 16, 2020 at 8:21 am
There's nothing in the original question that suggests that any rules of normalisation or anything else are being broken. The question isn't about how you store the data, it's about how you present it, in this case in a text file. And you present it, as Brian mentioned, in the format in which the end user asks for it.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply