Author |
Topic |
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-03 : 08:18:18
|
I have a simple query where two tables are joined (Host and Application), a Host can have mutiple applications. In the resulting query each line has two entries of Host and Application, however I need to somehow have one line for each host, and a column for each application.I know I can do this in a view by joining Host to multiple Application tables, however my problem is that there is no upper limit to the number of applications that a host can have.So my question is, how do I write a query in a view or using a stored procedure that will produce on eline for each host and show all the applications for that host on the same line.This data is for a report. I know I can do a matrix report to get the same effect, but our client does not what me to take that approach. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-03 : 21:43:32
|
something along lines of belowmake sure you put actual columnnames instead of indicative names i usedSELECT HostName,STUFF((SELECT ',' + applicationName FROM Application WHERE HostID = h.HostID FOR XML PATH('')),1,1,'')FROM Host h ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-04 : 06:07:08
|
quote: Originally posted by visakh16 something along lines of belowmake sure you put actual columnnames instead of indicative names i usedSELECT HostName,STUFF((SELECT ',' + applicationName FROM Application WHERE HostID = h.HostID FOR XML PATH('')),1,1,'')FROM Host h Thanks for that, however when I tried it I found a number of issues.There is 1 host with 30 applications, and your code produced 30 duplicate lines with two columns (Host, Application). All 30 applications were in the same column, seperated by a comma.Is it possible to have them in seperate columns and have the columns automatically named (ie Application 1, Application 2 etc)?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-06 : 05:04:39
|
Could you suggest a split fucntion to use to split the one column into multiple named columns? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-06 : 05:13:50
|
quote: Originally posted by sprotson Could you suggest a split fucntion to use to split the one column into multiple named columns?
Check these links:http://visakhm.blogspot.in/2010/02/parsing-delimited-string.htmlhttp://www.syntaxwarriors.com/2012/t-sql-splitting-strings-into-multiple-rows/--Chandu |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-06 : 05:20:17
|
Both those methods split the field into rows. I am looking for something that can split the field into multiple columns in the same row and be able to name the columns |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-06 : 05:52:08
|
I dont think a pivot is what i am looking formy csv has entries like so1,a,3,d,5,6,c,8,91,5,6,z,8I want these values split to columns, likeCol1 Col2 Col3 etc1 a 31 5 6So the first value in the csv goes into colu1 and so on |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-06 : 06:17:12
|
Use Import/Export Wizard or BULK INSERT command--Chandu |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-06 : 06:33:40
|
Sorry, it is not a CSV file it is a delimitted string in a table |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-06 : 12:02:56
|
quote: Originally posted by bandi
quote: Originally posted by sprotson Both those methods split the field into rows. I am looking for something that can split the field into multiple columns in the same row and be able to name the columns
means you are looking for pivoting?http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179839&SearchTerms=pivot--Chandu
Which is what i suggested in my earlier replyI think OP missed that ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-06 : 12:04:21
|
quote: Originally posted by sprotson I dont think a pivot is what i am looking formy csv has entries like so1,a,3,d,5,6,c,8,91,5,6,z,8I want these values split to columns, likeCol1 Col2 Col3 etc1 a 31 5 6So the first value in the csv goes into colu1 and so on
you've breaking your original problem into multiple heresince you've them as rows you dont need to built the delimited string at all if you want them in separate fields. you could simply use dynamic pivot solution i suggested earlierhttp://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-06 : 12:34:45
|
quote: Originally posted by visakh16
quote: Originally posted by sprotson I dont think a pivot is what i am looking formy csv has entries like so1,a,3,d,5,6,c,8,91,5,6,z,8I want these values split to columns, likeCol1 Col2 Col3 etc1 a 31 5 6So the first value in the csv goes into colu1 and so on
you've breaking your original problem into multiple heresince you've them as rows you dont need to built the delimited string at all if you want them in separate fields. you could simply use dynamic pivot solution i suggested earlierhttp://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
When I tried to pivot, the column names came from the Application values - this is not what I want.I want to be able to define what the column names are - eg App1, App2 etc and then populate these columns with the 1st App Name, 2nd App Name etc.Also the code I used for the dynamic pivot when used in SSRS did not allow me to select any column names.DECLARE @columns VARCHAR(8000)SELECT @columns = COALESCE(@columns + ',[' + cast(Application as varchar) + ']','[' + cast(Application as varchar)+ ']')FROM CMEHostApplicationGROUP BY ApplicationDECLARE @query VARCHAR(8000)SET @query = 'SELECT *FROM CMEHostApplicationPIVOT(MAX(Application)FOR [Application]IN (' + @columns + '))AS p'EXECUTE(@query)Sorry if I am coming across as a bit dumb here. my knowledge of SQL is very limited. Most of my experience has been in building basic views to use directly in reports, with a little bit of dabbling with stored procedures purely to pass parameters to reports. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-06 : 12:41:05
|
When I tried to pivot, the column names came from the Application values - this is not what I want.I want to be able to define what the column names are - eg App1, App2 etc and then populate these columns with the 1st App Name, 2nd App Name etc.you can always define the column names in way you want using column aliasesjust need to add that logic to @columns variable to make it like [ColumnName] AS yourNameso what determines the value you need to put in for column names? is it in another table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-11-06 : 12:51:10
|
quote: Originally posted by visakh16 When I tried to pivot, the column names came from the Application values - this is not what I want.I want to be able to define what the column names are - eg App1, App2 etc and then populate these columns with the 1st App Name, 2nd App Name etc.you can always define the column names in way you want using column aliasesjust need to add that logic to @columns variable to make it like [ColumnName] AS yourNameso what determines the value you need to put in for column names? is it in another table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I just want the column names to increment by 1, so if the max number if apps is 5, then the columns are 1,2,3,4,5.I don't think this is possible using a dynamic pivot hence why I then started looking at joining all the apps into a string, and then looked to split it out using a function based on the delimiter |
|
|
|