Display results as columns instead of rows

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

SQL Server 2005 Forums

Transact-SQL (2005)

Display results as columns instead of rows

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-11 : 14:37:32

Transpose the output

I have a sproc that takes a date and produces something like this
1AAA
2AAA
3AAA

If I run this sproc against some dynamic range of dates, I will have this result
1AAA
2AAA
3AAA
1BBB
2BBB
3BBB
1CCC
2CCC
3CCC
1DDD
2DDD
3DDD

AAA,BBB are just to indicate they belong to the same date.

I'd like to have my result displayed in this way (one column for a date)
1AAA 1BBB 1CCC 1DDD
2AAA 2BBB 2CCC 2DDD
3AAA 3BBB 3CCC 3DDD

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-11 : 16:23:33

For what it's worth, the sproc will return a fixed number of rows for each day. Say like 100

So each day, I will have 100 rows of data. Now the task is to arrange each 100 rows into column, one for each date. The date range is dynamic.

Thanks for any help.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-11 : 16:25:43

read about PIVOT

http://msdn.microsoft.com/en-us/library/ms177410.aspx

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-14 : 12:17:33

I did look at the pivot examples page but it doesn't have something similar to what i need

I have a table like this
2/10/2008 4.5
2/10/2008 3.7
2/9/2008 4.6
2/9/2008 5.5

I like to group the second column under the same date like this
2/10/2008 2/9/2008
4.5 4.6
3.7 5.5

So the result would be a table with the dates as first row and all data of that date will be in the same column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 13:43:19

will you be certain on number of records that will appear per date?what can be maximum number?

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-14 : 13:45:58

yes, the number of record per day is fixed at 60 and equal for each day. If we extend it, it will not exceed say 200.
Thanks visakh.

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-15 : 09:44:03

visakh16,
Let me know if you need any other info.
Thanks

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-15 : 16:01:46

I've banged my head against the wall the last few days but couldn't get this to work.
any expert can kindly chime in?
Thanks

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-16 : 12:33:24

visakh,
Wonder if you got a chance to see my response to your question.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 13:16:00

[code];With Your_CTE(RowNo,Date,Value)AS
(SELECT ROW_NUMBER() OVER(PARTITION BY Date ORDERBY Date),
Date,Value
FROM YourTable)
SELECT r.StrVal
FROM
(
SELECT RTRIM(dl.DateList) AS StrVal,1 as Ord
FROM (SELECT CAST(Date AS varchar(15)) + ' ' AS [text()]
FROM Your_CTE
WHERE RowNo=1
FOR XML PATH(''))dl(DateList)
UNION ALL
SELECT RTRIM(vl.ValueList),2
FROM (SELECT DISTINCT RowNo FROM Your_CTE) c1
CROSS APPLY(SELECT CAST(Value AS varchar(15)) + ' ' AS [text()]
FROM Your_CTE
WHERE RowNo=c1.RowNo
FOR XML PATH(''))vl(ValueList)
)r
ORDER BY r.Ord[/code]

and if possible you can even do this formatting at your front end.

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-16 : 13:46:07

visakh,
Many thanks for your help. It means a great deal to me. I'm surprised that you don't use any PIVOT at all.

Follow up questions:
1) How would I make it to display the date in desc order.
2) Formatting: right now, what I have it 63 rows of data, each row is a string.
To separate them each date and value, I use
(SELECT CAST(Date AS varchar(11)) + ', ' AS [text()]

This would separate each value by a comma.

Is it impossible to put them in columns so that I don't have to format in front end?
Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 13:48:22

quote:
Originally posted by daman

visakh,
Many thanks for your help. It means a great deal to me. I'm surprised that you don't use any PIVOT at all.

Follow up questions:
1) How would I make it to display the date in desc order.
2) Formatting: right now, what I have it 63 rows of data, each row is a string.
To separate them each date and value, I use
(SELECT CAST(Date AS varchar(11)) + ', ' AS [text()]

This would separate each value by a comma.

Is it impossible to put them in columns so that I don't have to format in front end?
Thanks again.

1.Add the ORDER BY inside the derived tables.
2.Whats the front end your using. it may be easier to do the formatting at front end.

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-16 : 14:08:41

1) Not sure I do the right thing but
;With Your_CTE(RowNo,Date,Value)AS
(SELECT ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Date DESC),

makes no difference
2) This sproc will put the data into a dynamic named range in Excel, one column for each date. Any suggestion to make this insertion seamless is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 14:14:07

quote:
Originally posted by daman

1) Not sure I do the right thing but
;With Your_CTE(RowNo,Date,Value)AS
(SELECT ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Date DESC),

makes no difference
2) This sproc will put the data into a dynamic named range in Excel, one column for each date. Any suggestion to make this insertion seamless is appreciated.

1.
;With Your_CTE(RowNo,Date,Value)AS
(SELECT ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Date DESC),
Date,Value
FROM YourTable)
SELECT r.StrVal
FROM
(
SELECT RTRIM(dl.DateList) AS StrVal,1 as Ord
FROM (SELECT CAST(Date AS varchar(15)) + ' ' AS [text()]
FROM Your_CTE
WHERE RowNo=1
ORDER BY Date DESC
FOR XML PATH(''))dl(DateList)
UNION ALL
SELECT RTRIM(vl.ValueList),2
FROM (SELECT DISTINCT RowNo FROM Your_CTE) c1
CROSS APPLY(SELECT CAST(Value AS varchar(15)) + ' ' AS [text()]
FROM Your_CTE
WHERE RowNo=c1.RowNo
ORDER BY Date DESC
FOR XML PATH(''))vl(ValueList)
)r
ORDER BY r.Ord

2. How are you exporting data to excel? Are you using bcp out method?

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-16 : 14:21:14

1) Thanks again
2) I have some VBA code that calls on the stored procedure and insert the data as record set into Excel sheet.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 14:27:00

quote:
Originally posted by daman

1) Thanks again
2) I have some VBA code that calls on the stored procedure and insert the data as record set into Excel sheet.

1. You're welcome

2. You can write a VBA function to perform the splitting of data to various cells.

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-16 : 14:37:04

Not to be a perfectionist but is there a reason you didn't use PIVOT?
Also, i'm fine with using VBA to format the data before importing them.
Just want to know if there is no better solution to this problem i.e a column for each day ;)

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-16 : 15:24:05

quote:
Originally posted by daman

I did look at the pivot examples page but it doesn't have something similar to what i need

I have a table like this
2/10/2008 4.5
2/10/2008 3.7
2/9/2008 4.6
2/9/2008 5.5

I like to group the second column under the same date like this
2/10/2008 2/9/2008
4.5 4.6
3.7 5.5

So the result would be a table with the dates as first row and all data of that date will be in the same column.

here's using PIVOT


CREATE TABLE TEMP_TAB (
  MY_DATE  DATETIME,
  MY_VALUE FLOAT)

INSERT INTO TEMP_TAB (MY_DATE, MY_VALUE)
SELECT '2/10/2008', 4.5
UNION ALL
SELECT '2/10/2008', 3.7
UNION ALL
SELECT '2/9/2008',  4.6
UNION ALL
SELECT '2/9/2008',  5.5

SELECT * FROM TEMP_TAB

DECLARE  @cols NVARCHAR(2000)

SELECT   @COLS = COALESCE(@COLS + ',[' + CONVERT(VARCHAR(10),A.MY_DATE,101) + ']',
                          '[' + CONVERT(VARCHAR(10),A.MY_DATE,101) + ']')
FROM     (SELECT DISTINCT MY_DATE AS MY_DATE
          FROM   TEMP_TAB) A
ORDER BY A.MY_DATE

DECLARE  @query NVARCHAR(4000)

SET @query = N'SELECT ' + @cols + '
			FROM    (	select row_number() over (partition by my_date order by my_date) as rowid, my_date, my_value from temp_tab 
					) p PIVOT ( SUM(my_value)
								FOR [my_date] 
								  IN (' + @cols + ' ) 
			) AS pvt;'

EXECUTE( @query)

DROP TABLE TEMP_TAB

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-16 : 15:38:29

Holly cows, Rohit !
That's awesome.

Thanks to both of you Visakh and Rohit. You made my day.

daman
Yak Posting Veteran

72 Posts

Posted - 2008-07-17 : 17:03:11

Rohit,
I sometimes has this error. I'm not sure it has anything to do with the data on that date. If I take a look at the date in question, there is a row with this data format
-8.24316168954913E-05

Would it be a concern?

Msg 103, Level 15, State 4, Line 1
The identifier that starts with '11/16/2007
FROM ( select row_number() over (partition by my_date order by my_date) as rowid, my_date, my_value from temp_' is too long. Maximum length is 128.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'IN'.
Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark after the character string '12/1'.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-17 : 17:21:05

can you post the full query you're using?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources