September 30, 2011 at 9:53 am
I'm looking for a query to take the following table
CREATE TABLE MySample (Id Int, Field varchar(50), Contents varchar(500))
INSERT INTO MySample VALUES (1, 'OS Name','Windows')
INSERT INTO MySample VALUES (2, 'Processors','Processor1')
INSERT INTO MySample VALUES (3, 'Processors','Processor2')
INSERT INTO MySample VALUES (4, 'Time Zone','Central')
INSERT INTO MySample VALUES (5, 'HotFixes','HotFix [1]')
INSERT INTO MySample VALUES (6, 'HotFixes','HotFix [2]')
INSERT INTO MySample VALUES (7, 'HotFixes','HotFix [3]')
INSERT INTO MySample VALUES (8, 'HotFixes','HotFix [4]')
INSERT INTO MySample VALUES (9, 'HotFixes','HotFix [5]')
And get results that look like this
FieldContents
----------------------------------
OS NameWindows
----------------------------------
ProcessorsProcessor1
Processor2
----------------------------------
Time ZoneCentral
----------------------------------
HotFixesHotFix [1]
HotFix [2]
HotFix [3]
HotFix [4]
HotFix [5]
----------------------------------
Where the dashed lines represent row breaks.
In other words if there is more than one row with the same field name I want them concatenated together with carriage returns between in the order of ID in a result set.
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 30, 2011 at 10:09 am
Great job psoting sample Data, Kenneth;
what you are looking for is to use the FOR XML concatination technique.
based on your sample data, this seems to work perfectly;
/*
--Results
Field Skills
HotFixes HotFix [1],HotFix [2],HotFix [3],HotFix [4],HotFix [5]
OS Name Windows
Processors Processor1,Processor2
Time Zone Central
*/
SELECT Field,stuff(( SELECT ',' + Contents
FROM MySample s2
WHERE s2.Field= s1.Field --- must match GROUP BY below
ORDER BY Contents
FOR XML PATH('')
),1,1,'') as [Skills]
FROM MySample s1
GROUP BY s1.Field --- without GROUP BY multiple rows are returned
ORDER BY s1.Field
Lowell
September 30, 2011 at 10:13 am
Carriage returns version: -
DECLARE @MySample AS TABLE (Id Int, Field varchar(50), Contents varchar(500))
INSERT INTO @MySample VALUES (1, 'OS Name','Windows')
INSERT INTO @MySample VALUES (2, 'Processors','Processor1')
INSERT INTO @MySample VALUES (3, 'Processors','Processor2')
INSERT INTO @MySample VALUES (4, 'Time Zone','Central')
INSERT INTO @MySample VALUES (5, 'HotFixes','HotFix [1]')
INSERT INTO @MySample VALUES (6, 'HotFixes','HotFix [2]')
INSERT INTO @MySample VALUES (7, 'HotFixes','HotFix [3]')
INSERT INTO @MySample VALUES (8, 'HotFixes','HotFix [4]')
INSERT INTO @MySample VALUES (9, 'HotFixes','HotFix [5]')
SELECT a.Field
,STUFF((
SELECT CHAR(13) + b.Contents
FROM @MySample b
WHERE a.Field = b.Field
FOR XML PATH('')
,TYPE
).value('.', 'VARCHAR(8000)'), 1, 1, '') AS Contents
FROM @MySample a
To see the carriage returns, you'd have to execute with "Results to Text".
September 30, 2011 at 10:15 am
Try this:
1. This will be fruitful and can see the results in TEXT mode.
SELECT p1.Field,
STUFF ( ( SELECT ','+ Contents+ CHAR(13)
FROM #MySample p2
WHERE p2.Field = p1.Field
ORDER BY Contents
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
FROM #MySample p1
GROUP BY p1.Field ;
2. This may just be a way in Grid mode to display per your request.
; with cte as
(
select * , rn = ROW_NUMBER() over(partition by Field order by id )
from #MySample
)
select Field = case when rn = 1 then Field else '' end
,Contents
from cte
Now, if you want to insert those "dashed line breaks" then that is also quite possible 🙂
{Edit: Replaced CHAR(10) with CHAR(13) for Carriage Return after seeing Cadavre's post 🙁 sorry for the churn}
September 30, 2011 at 11:51 am
Awsome stuff, thank you all. Obviously I need to read up on FOR XML more, I didn't realize it was so useful even when you arn't working directly with XML.
I ended up using
SELECT a.Field
,STUFF((
SELECT CHAR(13) + b.Contents
FROM @MySample b
WHERE a.Field = b.Field
FOR XML PATH('')
,TYPE
).value('.', 'VARCHAR(8000)'), 1, 1, '') AS Contents
FROM @MySample a
since it fit my needs best. I'm actually running the systeminfo command using xp_cmdshell then dumping the data into a table, then parsing it. I'm trying to store the information for a number of different servers that way.
I should probably look at a Policy methodology (we have a few 2008 servers) but this is what I have so far.
Thanks again
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 30, 2011 at 12:46 pm
Kenneth Fisher-475792 (9/30/2011)
I'm looking for a query to take the following table
This is a report and you should be using a reporting tool to generate it. T-SQL is not a reporting tool.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 3, 2011 at 8:36 am
drew.allen (9/30/2011)
Kenneth Fisher-475792 (9/30/2011)
I'm looking for a query to take the following tableThis is a report and you should be using a reporting tool to generate it. T-SQL is not a reporting tool.
Drew
The end results will be used to supply periodic requests from management. For example: What OS do your SQL Servers run on? How much memory do they have? etc. Not a general report.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 3, 2011 at 9:45 am
Kenneth Fisher-475792 (10/3/2011)
drew.allen (9/30/2011)
Kenneth Fisher-475792 (9/30/2011)
I'm looking for a query to take the following tableThis is a report and you should be using a reporting tool to generate it. T-SQL is not a reporting tool.
Drew
The end results will be used to supply periodic requests from management. For example: What OS do your SQL Servers run on? How much memory do they have? etc. Not a general report.
Kenneth
It's going to management? Definitely a REPORT!
The whole purpose of a reporting tool is to take raw data and present it in a way that is more legible. That's exactly what you are trying to do, but you're using a tool that was not designed for that purpose.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 3, 2011 at 12:53 pm
Actually no, in this case a reporting tool won't work. We get the occasional spreadsheet sent to us by upper management with the task of "filling in the blanks". If we turn around and hand them a report they probably wouldn't appreciate it. Not to mention that I'm working on the data collection right now. Not the output.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 3, 2011 at 1:32 pm
Kenneth Fisher-475792 (10/3/2011)
Actually no, in this case a reporting tool won't work. We get the occasional spreadsheet sent to us by upper management with the task of "filling in the blanks". If we turn around and hand them a report they probably wouldn't appreciate it. Not to mention that I'm working on the data collection right now. Not the output.
Just because it takes the form of a spreadsheet, doesn't mean that it's not a report. All of the reporting tools that I have worked with are capable of exporting to spreadsheets.
"Filling in the blanks" can even be accomplished by a report provided you have access to the necessary information. Using a report to "fill in the blanks" has the benefit of not introducing transcription errors.
Your original post was not about data collection, it was about formatting your already collected data. Formatting is a function of a reporting tool.
Of course, I can't speak to your specific situation, but nothing that you have written so far categorically excludes using a reporting tool.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 3, 2011 at 2:34 pm
drew.allen (10/3/2011)
Just because it takes the form of a spreadsheet, doesn't mean that it's not a report. All of the reporting tools that I have worked with are capable of exporting to spreadsheets."Filling in the blanks" can even be accomplished by a report provided you have access to the necessary information. Using a report to "fill in the blanks" has the benefit of not introducing transcription errors.
Absolutely. But in this case we are one of dozens of people filling in the spreadsheet. We could create a report to duplicate their spreadsheet, but we would have to take into account data that already exists in the spreadsheet, and changes that are occurring on the original, which we are expected to update. It's easier to copy and past a block of information from a query. Since we are doing a copy and paste from a table the data entry mistakes should be fairly minimal.
I am however a firm believer in reports and reporting tools and would agree with you in most cases.
Your original post was not about data collection, it was about formatting your already collected data. Formatting is a function of a reporting tool.
Actually I am formatting raw data I am getting from the dos command "systeminfo" and putting into a table to make it more convinient for queries. We don't have any specific need for it right now, but we do get requests several times a year for the information. Since we are working with 60-70 servers it will be easier to run a query on this new table (or dump it to a report) than touching each server individually to collect information such as OS, Memory etc.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply