Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple rows in one row (SP or View)

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 below

make sure you put actual columnnames instead of indicative names i used

SELECT HostName,
STUFF((SELECT ',' + applicationName FROM Application WHERE HostID = h.HostID FOR XML PATH('')),1,1,'')
FROM Host h


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-04 : 06:07:08
quote:
Originally posted by visakh16

something along lines of below

make sure you put actual columnnames instead of indicative names i used

SELECT 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 MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 11:35:14
what about this?

SELECT HostName,
STUFF((SELECT ',' + applicationName FROM Application WHERE HostID = h.HostID FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT HostID,HostName FROM Host) h


for naming application as 1,2 etc you would require getting them in separate columns which is kind of cross tabbing
if you want that you can use solution like

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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?
Go to Top of Page

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.html
http://www.syntaxwarriors.com/2012/t-sql-splitting-strings-into-multiple-rows/

--
Chandu
Go to Top of Page

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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-06 : 05:43:26
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
Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-11-06 : 05:52:08
I dont think a pivot is what i am looking for

my csv has entries like so

1,a,3,d,5,6,c,8,9
1,5,6,z,8

I want these values split to columns, like

Col1 Col2 Col3 etc
1 a 3
1 5 6

So the first value in the csv goes into colu1 and so on
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-06 : 06:17:12
Use Import/Export Wizard or BULK INSERT command

--
Chandu
Go to Top of Page

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
Go to Top of Page

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 reply
I think OP missed that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 for

my csv has entries like so

1,a,3,d,5,6,c,8,9
1,5,6,z,8

I want these values split to columns, like

Col1 Col2 Col3 etc
1 a 3
1 5 6

So the first value in the csv goes into colu1 and so on


you've breaking your original problem into multiple here

since 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 earlier

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 for

my csv has entries like so

1,a,3,d,5,6,c,8,9
1,5,6,z,8

I want these values split to columns, like

Col1 Col2 Col3 etc
1 a 3
1 5 6

So the first value in the csv goes into colu1 and so on


you've breaking your original problem into multiple here

since 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 earlier

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 CMEHostApplication
GROUP BY Application


DECLARE @query VARCHAR(8000)

SET @query = '
SELECT *
FROM CMEHostApplication
PIVOT
(
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.
Go to Top of Page

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 aliases

just need to add that logic to @columns variable to make it like [ColumnName] AS yourName

so what determines the value you need to put in for column names? is it in another table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 aliases

just need to add that logic to @columns variable to make it like [ColumnName] AS yourName

so what determines the value you need to put in for column names? is it in another table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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
Go to Top of Page
   

- Advertisement -