display query result vertically

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

display query result vertically

Author

Topic

Randjana
Starting Member

9 Posts

Posted - 2008-03-27 : 14:26:17

Hi,

I have a query to run, but the data in the tables are stored horizontally. I want the query to output the result vertically.

e.g. if row 1 contains the following data:
custA,3-april2008,mango's,123,456,78,10

Then i want it to output as follows:
custA,3-april2008,mango's,123
custA,3-april2008,mango's,456
custA,3-april2008,mango's,78
custA,3-april2008,mango's,10

hope I'm clear, and would appreciate if someone could help me.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 14:43:22

Make a table valued function which accepts a comma seperated string of values ,seperates the values into a table and returns it. Take an inner join with the table valued function to get the required output. You can find different types of such function by searching within the forum.

a typical functioon will be of the form

CREATE FUNCTION GetValues
(@Customer varchar(10),
@IDList varchar(8000)
)
RETURNS @Results Table
(Customer varchar(10),
ID int
)
AS
BEGIN
WHILE @IDList IS NOT NULL
BEGIN
SELECT @Value=CASE
WHEN CHARINDEX(',',@IDList)> 0 THEN CAST(LEFT(@IDList,CHARINDEX(',',@IDList)-1) AS int)
ELSE CAST(@IDList AS int)
END,

@IDList=CASE WHEN CHARINDEX(',',@IDList)> 0 THEN CAST(SUBSTRING(@IDList,CHARINDEX(',',@IDList)+1,LEN(@IDList) AS int)
ELSE NULL
END
INSERT INTO @RESULTS
SELECT @Customer,@Value
END
RETURN
END
GO

ANd you can call this as

SELECT t.Customer,t.Date,t.Item,f.ID
FROM YourTable t
INNER JOIN dbo.GetValues(t.Customer,t.Values) f
ON f.Customer=t.Customer

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-28 : 02:23:43

If 2005, try using PIVOT

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-28 : 04:12:50

quote:
Originally posted by pravin14u

If 2005, try using PIVOT

...UNPIVOT

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources