rows become columns help!

I am building a calendar table for the most reason four weeks activitis and I have had a temp table data in table A (See my attached file) and I want to
make it as the format in table B as final. How to convert it? Please help!
Thank you!
Suincut this and paste

This should get you on your way.

More formating needed

select
case when dow = 1 then vactype else null end MON,
case when dow = 2 then vactype else null end TUES,
case when dow = 3 then vactype else null end WED,
case when dow = 4 then vactype else null end THUR,
case when dow = 5 then vactype else null end FRI,
case when dow = 6 then vactype else null end SAT,
case when dow = 7 then vactype else null end SUN,
nameitem,
weeknum
from(
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/1/2005' AS FULLWORK,
datepart(dw,'1/1/2005') AS DOW,
1 as weeknum
union
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/2/2005' AS FULLWORK,
datepart(dw,'1/2/2005') AS DOW,
2 as weeknum
union
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/3/2005' AS FULLWORK,
datepart(dw,'1/3/2005') AS DOW,
2 as weeknum
union
select null AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/4/2005' AS FULLWORK,
datepart(dw,'1/4/2005') AS DOW,
2 as weeknum
union
select null AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/5/2005' AS FULLWORK,
datepart(dw,'1/5/2005') AS DOW,
2 as weeknum) a|||thanks thanks!

rows as columns

is it possible to write a query so that we can have all rows of one column in a single column
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable

Rows as Columns

This summary is not available. Please click here to view the post.

Rows and Columns

I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?Are you using SQL Server 2005 or SQL Server 2000?|||

Hi,

in SQL Server 200 oyu will have to use appropiate CASE expressions, in SQL Server 2005 you can use the new PIVOT functionality-

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am using SQL 2000 which someone else said needed a CASE statement. What I have seen seems to want to add things. What I have is a list of numbers and then columns that are the results of my Query.

I have columns named Store , Name, Net, Tax, Deposits (and a bunch of others)

I have rows with the store numbers 2, 3, 4, 5 ,6 that I sort by

I would like the columns as rows and the store numbers across the top

Thanks

sql

Row Total In Table

Hi,
I have a table in my report (not a matrix) that displays 50 rows by
6 columns - fixed, no grouping requirements. The user wants to see the
total of both the vertical column (easy) and also the horizontal row - not
so easy.
First I tried this expression = SUM(Fields!Field1.Value +
Fields!Fields2.Value....etc) but this did not give the correct number.
A co-worker told me that a horizontal row sum was "impossible" to do
with a table and that I had to swap my table for a matrix.
Can anyone tell me how to get the sum of a horizontal row of a
table?
JDJust try this,
= SUM(Fields!Field1.Value +sum(Fields!Fields2.Value) + ....etc
Amarnath
"Joe Delphi" wrote:
> Hi,
> I have a table in my report (not a matrix) that displays 50 rows by
> 6 columns - fixed, no grouping requirements. The user wants to see the
> total of both the vertical column (easy) and also the horizontal row - not
> so easy.
> First I tried this expression = SUM(Fields!Field1.Value +
> Fields!Fields2.Value....etc) but this did not give the correct number.
> A co-worker told me that a horizontal row sum was "impossible" to do
> with a table and that I had to swap my table for a matrix.
> Can anyone tell me how to get the sum of a horizontal row of a
> table?
>
> JD
>
>|||Sorry, Sending you again
Just try this
= SUM(Fields!Field1.Value) + SUM(Fields!Fields2.Value) + ......etc
Amarnath
"Joe Delphi" wrote:
> Hi,
> I have a table in my report (not a matrix) that displays 50 rows by
> 6 columns - fixed, no grouping requirements. The user wants to see the
> total of both the vertical column (easy) and also the horizontal row - not
> so easy.
> First I tried this expression = SUM(Fields!Field1.Value +
> Fields!Fields2.Value....etc) but this did not give the correct number.
> A co-worker told me that a horizontal row sum was "impossible" to do
> with a table and that I had to swap my table for a matrix.
> Can anyone tell me how to get the sum of a horizontal row of a
> table?
>
> JD
>
>|||To get the horizontal row sume miss out the SUM(). Just use the expression
=Fields!Field1.Value+Fields!Field2.Value+...+Fields!Field6.Value
HTH,
Magendo_Man
"Joe Delphi" wrote:
> Hi,
> I have a table in my report (not a matrix) that displays 50 rows by
> 6 columns - fixed, no grouping requirements. The user wants to see the
> total of both the vertical column (easy) and also the horizontal row - not
> so easy.
> First I tried this expression = SUM(Fields!Field1.Value +
> Fields!Fields2.Value....etc) but this did not give the correct number.
> A co-worker told me that a horizontal row sum was "impossible" to do
> with a table and that I had to swap my table for a matrix.
> Can anyone tell me how to get the sum of a horizontal row of a
> table?
>
> JD
>
>

row too long when exporting to PDF

if I have a table with 3 rows and 3 columns and the columns are wide so that
2 columns just fit on one page.By default reporting server, when exporting to
PDF, puts first 3 rows and 2 columns on one page and last column for all the
rows on second
first page:
header1 header2
1row 1col 1row 2 col
2row 1 col 2row 2 col
second page:
header 3
1 row 3 col
2 row 3 col
Is it possible to do so that it splits the data for all rows separately:
header 1 header 2
1 row 1 col 1 row 2 col
header 3
1 row 3 col
header 1 header 2
2 row 1 col 2 row 2 col
header 3
2 row 3 colOn May 16, 9:49 am, pardik <par...@.discussions.microsoft.com> wrote:
> if I have a table with 3 rows and 3 columns and the columns are wide so that
> 2 columns just fit on one page.By default reporting server, when exporting to
> PDF, puts first 3 rows and 2 columns on one page and last column for all the
> rows on second
> first page:
> header1 header2
> 1row 1col 1row 2 col
> 2row 1 col 2row 2 col
> second page:
> header 3
> 1 row 3 col
> 2 row 3 col
> Is it possible to do so that it splits the data for all rows separately:
> header 1 header 2
> 1 row 1 col 1 row 2 col
> header 3
> 1 row 3 col
> header 1 header 2
> 2 row 1 col 2 row 2 col
> header 3
> 2 row 3 col
You might try grouping on a report item at the row level. Then set
'Page break at end' as part of the grouping. If this does not work,
you could try using a matrix control and rearranging your dataset
(query/stored procedure) output to format the matrix in the above
format. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Row to column

Is there a row to column function?

I need to convert some rows into columns in a stored proc.

An y ideas.

Thanks,

Gene

If you're using SQL2005 there is the PIVOT/UNPIVOT functions which are explained nicely in Books Online...
|||

if there is multiple columns need to be pivoted, I recommand to use the legacy approach rather new PIVOT operator,

Code Snippet

Create Table #UnPivot

(

Year int,

Product int,

Sales int,

Qty int

)

Insert Into #UnPivot Values(2004,1,28,67);

Insert Into #UnPivot Values(2005,1,15,20);

Insert Into #UnPivot Values(2006,1,50,30);

Insert Into #UnPivot Values(2004,2,5,67);

Insert Into #UnPivot Values(2005,2,6,20);

Insert Into #UnPivot Values(2006,2,10,30);

Select

Product

,Max(Case When Year=2004 then Sales End) [2004-Sales]

,Max(Case When Year=2004 then Qty End) [2004-Qty]

,Max(Case When Year=2005 then Sales End) [2005-Sales]

,Max(Case When Year=2005 then Qty End) [2005-Qty]

,Max(Case When Year=2006 then Sales End) [2006-Sales]

,Max(Case When Year=2006 then Qty End) [2006-Qty]

From

#UnPivot

Group By

product

Drop Table #UnPivot

sql

Row size limitations of SQL Server 2005

I've created a database design and I'm trying to figure out if it's workable or not. I have 2 tables in particular that are sparse (20 columns, but only a few have data - about 100 bytes/row) but will grow very large - to the tune of 700 million rows.

My question is whether or not there is a problem with SQL Server 2005 having 1.5+ billion rows of data even though it will likely only take up 100 gigs or so on disk. Anyone have experience in large numbers of rows like this? We're still doing testing as far determining how slow queries will get.

Thanks,

Craig

That's fine. SQL Server can deal with that many rows of data. I've worked with tables with billions of rows before. Don't forget to plan for your index space as well and any necessary index maintenance.

-Sue

|||

Try the link below for the SQL Server Max CAP.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Row number in the dataflow task

Hello all,

I got a text file with two columns. and I need to generate a integer key automatically with the row number (or any distinct number, I thought row number will be OK). and when I make the data flow task to import this text file into a raw file I need to get the unique rownumber as Id.
How can I make this in the data flow tak?

regards,

This should work for you:

http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

|||

Did you try searching the forum? That question has been asked before and several are the ways of doing it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379124&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1533040&SiteID=1

|||

Thank you.....

I am so happy with the script componant......

It works good.....

Thank you again.

Regards

ROW Limit and pages

OK I just need a clarification:
SO the total size of the combined columns in a table for one row cannot
exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
Thank you in advance!
Mike
On Mon, 22 Nov 2004 12:17:59 -0500, Mike London wrote:

>OK I just need a clarification:
>SO the total size of the combined columns in a table for one row cannot
> exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
>Thank you in advance!
Hi Mike,
NCHAR should be counted in, IMAGE should be thrown out (as well as TEXT
and NTEXT) (but do check out the text in row option).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you both for that clarification!
Mike London wrote:
> OK I just need a clarification:
> SO the total size of the combined columns in a table for one row cannot
> exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
> Thank you in advance!
> Mike

ROW Limit and pages

OK I just need a clarification:
SO the total size of the combined columns in a table for one row cannot
exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
Thank you in advance!
MikeYes,
But its 8038
Yours in quibble
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
Peter The Spate
>--Original Message--
>OK I just need a clarification:
>SO the total size of the combined columns in a table for
one row cannot
> exceed 8k correct? (Throwing out the TEXT, NCHAR and
NTEXT data types)
>Thank you in advance!
>Mike
>.
>|||On Mon, 22 Nov 2004 12:17:59 -0500, Mike London wrote:
>OK I just need a clarification:
>SO the total size of the combined columns in a table for one row cannot
> exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
>Thank you in advance!
Hi Mike,
NCHAR should be counted in, IMAGE should be thrown out (as well as TEXT
and NTEXT) (but do check out the text in row option).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you both for that clarification!
Mike London wrote:
> OK I just need a clarification:
> SO the total size of the combined columns in a table for one row cannot
> exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
> Thank you in advance!
> Mike

ROW Limit and pages

OK I just need a clarification:
SO the total size of the combined columns in a table for one row cannot
exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
Thank you in advance!
MikeOn Mon, 22 Nov 2004 12:17:59 -0500, Mike London wrote:

>OK I just need a clarification:
>SO the total size of the combined columns in a table for one row cannot
> exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
>Thank you in advance!
Hi Mike,
NCHAR should be counted in, IMAGE should be thrown out (as well as TEXT
and NTEXT) (but do check out the text in row option).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you both for that clarification!
Mike London wrote:
> OK I just need a clarification:
> SO the total size of the combined columns in a table for one row cannot
> exceed 8k correct? (Throwing out the TEXT, NCHAR and NTEXT data types)
> Thank you in advance!
> Mike

Row and column count in SSMS

When you execute an MDX query the Messages tab displays the number of rows and columns returned.

However, that includes the rows and columns used to hold the member names. Personally I would prefer it only returned the rows and columns in the cellset.

Anyone agree/disagree? Discuss!!!

Also, a minor point. I'd like it if the rows and columns were displayed on the results tab like the number of rows are in a SQL query. Anyone agree?

cheers

Jamie

Hi Jamie,

The row and column counts returned for empty axes are also a bit inconsistent :

>>

select {} on 0
from [Adventure Works]
-
Cell set consists of 1 rows and 0 columns.

-
select {} on 0,
{} on 1
from [Adventure Works]
-
Cell set consists of 0 rows and 0 columns.

>>

The separate Message Tab takes some getting used to, but it works OK for me...

|||

Interesting, thanks Deepak.

Anyone else?

Round funtion on entire columns in MSSQL?

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steveOn 5 May 2004 02:40:15 -0700, steve wrote:

>Hi,
>I'd like to round all amounts in a certain column to 2 decimals.
>I tried the following query, but eventhough the syntax is correct, it
>doesn't give any result:
>update gbkmut
>set bdr_hfl = round(bdr_hfl,2)
>can anyone help me?
>cheers,
>steve

Hi Steve,

What do you mean with "doesn't give any result"?

If you mean that no rows were returned by the update statement, then
this is expected behaviour. An UPDATE-statement will update the data,
nothing more nothing less. Use SELECT if you want to see anything.

If you mean that after executing the above UPDATE, you still have data
with more than two non-zero digits after the decimal point, I'd ask
you to post more details (table definition in the form of CREATE TABLE
statements, sample data in the form of INSERT statements, expected
output and the output you got) so that others can try if they can
reproduce this apparantly erroneous behaviour.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||stefan.van.den.steen@.exact.be (steve) wrote in message news:<79d8a1b0.0405050140.6f442694@.posting.google.com>...
> Hi,
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)
> can anyone help me?
> cheers,
> steve

At first glance, your UPDATE statement seems to be OK. Can you give
some more information? In particular, what is the data type of the
bdr_hfl column, and can you give some sample values, as well as your
expected result? And what does "doesn't give any result" mean?

Simon|||steve (stefan.van.den.steen@.exact.be) writes:
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)

There is very little information, but I would guess that your column is
of datatype float. Float is an approxamite datatype, which means that
far from all values can be stored exactly in a float. For instance,
try this:

select convert(float, 1.89)

In Query Analyzer this displays as 1.8899999999999999.

In many situations, it is possible to cope with these extra decimals at
the end; you only need some care. If you need exact numbers, you must
use the decimal type instad. The drawback is that with decimal, you
must decide from the beginning which range you handle.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Rotating information from Recordsets to columns.

Hello,

This problem perplexes me and I hope that someone has done something
efficient.

Take for example the data in the MASTER..SYSPERFINFO:
SELECT
CAST(RTRIM(INSTANCE_NAME) AS VARCHAR(15)),
CAST(RTRIM(COUNTER_NAME) AS VARCHAR(31)),
CAST(RTRIM(CNTR_VALUE) AS VARCHAR(10))
FROM MASTER..SYSPERFINFO
WHERE INSTANCE_NAME = N'TEMPDB'

tempdb Data File(s) Size (KB) 51200
tempdb Log File(s) Size (KB) 1272
tempdb Log File(s) Used Size (KB) 738
tempdb Percent Log Used 58
tempdb Active Transactions 0
tempdb Transactions/sec 186281
tempdb Repl. Pending Xacts 0
tempdb Repl. Trans. Rate 0
tempdb Log Cache Reads/sec 0
tempdb Log Cache Hit Ratio 0
tempdb Log Cache Hit Ratio Base 0
tempdb Bulk Copy Rows/sec 0
tempdb Bulk Copy Throughput/sec 0
tempdb Backup/Restore Throughput/sec 0
tempdb DBCC Logical Scan Bytes/sec 0
tempdb Shrink Data Movement Bytes/sec 0
tempdb Log Flushes/sec 1578
tempdb Log Bytes Flushed/sec 67882496
tempdb Log Flush Waits/sec 226
tempdb Log Flush Wait Time 47
tempdb Log Truncations 248
tempdb Log Growths 3
tempdb Log Shrinks 0

<I did the CAST and LTRIM so that it looks better when displayed in a
browser
I would like to keep statistics in a table with the following columns:
INSTANCE_NAME,
DATA_FILE_SIZE,
LOG_FILE_FIZE,
ACTIVE_TRANS,
TRANS_PER_SEC

So, instead of having a table with three columns and 23 rows(only 4 of
which I want), I would have a single row with 4 columns(plus the
Instance_Name).

Visualy, I want to call this a 90 degree rotation. Here's what the
select statement would then look like:
SELECT *
FROM SYSPERFINFO_ARCHIVE
WHERE INSTANCE_NAME = N'TEMPDB'

Here's the result set:
tempdb 51200 1272 0 185198

Is it possible to 'rotate' a recordset into columns?
How would it be done?

Gracias.Tim (google_mssql2000@.cfapostle.com) writes:
> So, instead of having a table with three columns and 23 rows(only 4 of
> which I want), I would have a single row with 4 columns(plus the
> Instance_Name).
> Visualy, I want to call this a 90 degree rotation. Here's what the
> select statement would then look like:
> SELECT *
> FROM SYSPERFINFO_ARCHIVE
> WHERE INSTANCE_NAME = N'TEMPDB'
> Here's the result set:
> tempdb 51200 1272 0 185198

select a.instance_name,
data_file_size = a.cntr_value,
log_file_size = b.cntr_value,
active_trans = c.cntr_value,
trans_per_sec = d.cntr_value
from master..sysperfinfo a
join master..sysperfinfo b on a.instance_name = b.instance_name
join master..sysperfinfo c on a.instance_name = c.instance_name
join master..sysperfinfo d on a.instance_name = d.instance_name
where a.instance_name = N'tempdb'
and a.counter_name = N'Data File(s) Size (KB)'
and b.counter_name = N'Log File(s) Size (KB)'
and c.counter_name = N'Active Transactions'
and d.counter_name = N'Transactions/sec'

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message
> select a.instance_name,
> data_file_size = a.cntr_value,
> log_file_size = b.cntr_value,
> active_trans = c.cntr_value,
> trans_per_sec = d.cntr_value
> from master..sysperfinfo a
> join master..sysperfinfo b on a.instance_name = b.instance_name
> join master..sysperfinfo c on a.instance_name = c.instance_name
> join master..sysperfinfo d on a.instance_name = d.instance_name
> where a.instance_name = N'tempdb'
> and a.counter_name = N'Data File(s) Size (KB)'
> and b.counter_name = N'Log File(s) Size (KB)'
> and c.counter_name = N'Active Transactions'
> and d.counter_name = N'Transactions/sec'

Wow!. There are times I feel like a complete newbie. Hats off to the
set based thinkers as opposed to us poor old procedural bods.