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 Limit?

The following works on my server:
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
WHERE sm_pk = @.id
BUT, at my client's server it does NOT - no errors given, just it is left
NULL. To work I have to change the 2000 (in convert) to 200.
Does he has some row size limit. Where can I look?
Evanpermissions for client?
"Evan Camilleri" wrote:

> The following works on my server:
> UPDATE tb_tmp
> SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
> WHERE sm_pk = @.id
>
> BUT, at my client's server it does NOT - no errors given, just it is left
> NULL. To work I have to change the 2000 (in convert) to 200.
> Does he has some row size limit. Where can I look?
>
> Evan
>
>|||The row size limit in 2000 and below is 8060 bytes (including overhead). In
2005, you can overflow
the regular variable length datatypes.
Either way, the max row size does not include text, ntext, image, and the ne
w varchar(max),
nvarchar(max) and varbinary(max).
If it were an overflow problem, SQL Server would return an error message. Pe
rhaps the client
application suppresses this error? Did you execute the UPDATE from Query ana
lyzer, or? Or perhaps
there's a trigger on the table which silently modifies the column value?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Evan Camilleri" <e70mt@.yahoo.co.uk.nospam> wrote in message
news:%23hTaOk4kGHA.3924@.TK2MSFTNGP03.phx.gbl...
> The following works on my server:
> UPDATE tb_tmp
> SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
> WHERE sm_pk = @.id
>
> BUT, at my client's server it does NOT - no errors given, just it is left
NULL. To work I have to
> change the 2000 (in convert) to 200.
> Does he has some row size limit. Where can I look?
>
> Evan
>|||I do not think permissions have to do with it since CONVERT(NVARCHAR(200),
@.data) works (with 200 it works, with 2000 it does not)
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:41715CFE-A538-46A4-B17E-9BBC8B383342@.microsoft.com...
> permissions for client?
>
> "Evan Camilleri" wrote:
>|||Check the table definition, also look for any triggers.
ML
http://milambda.blogspot.com/|||
Thanks for your reply. We used Query Analyzer. There is no triggers.
Before this sp is executed I DROP the table and recreate it in the sp
itself... so there is no trigger.
DROP TABLE tb_tmp
CREATE TABLE tb_tmp (
[sm_pk] [int] NOT NULL,
[sm_Ref] [nvarchar] (50) NULL ,
[sm_PublicText] text NULL
)
ALTER TABLE tb_tmp WITH NOCHECK ADD CONSTRAINT [PK_tb_SM] PRIMARY
KEY CLUSTERED ([sm_pk])
INSERT INTO tb_tmp (sm_pk, sm_PublicText)
VALUES (@.id, '')
.......................................SET @.data here
--PRINT @.data ....................prints correctly
-- Save
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(4000), @.data)
WHERE sm_pk = @.id
As I said this works on my server BUT DOES NOT on the server of the client.
It leaves the row NULL. If I change the 4000 to 500 it works (obviously
truncating my records)
Evan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23K3%23I64kGHA.3596@.TK2MSFTNGP05.phx.gbl...
> The row size limit in 2000 and below is 8060 bytes (including overhead).
> In 2005, you can overflow the regular variable length datatypes.
> Either way, the max row size does not include text, ntext, image, and the
> new varchar(max), nvarchar(max) and varbinary(max).
> If it were an overflow problem, SQL Server would return an error message.
> Perhaps the client application suppresses this error? Did you execute the
> UPDATE from Query analyzer, or? Or perhaps there's a trigger on the table
> which silently modifies the column value?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Evan Camilleri" <e70mt@.yahoo.co.uk.nospam> wrote in message
> news:%23hTaOk4kGHA.3924@.TK2MSFTNGP03.phx.gbl...
>|||this is the SP:
DROP TABLE tb_tmp
CREATE TABLE tb_tmp (
[sm_pk] [int] NOT NULL,
[sm_Ref] [nvarchar] (50) NULL ,
[sm_PublicText] text NULL
)
ALTER TABLE tb_tmp WITH NOCHECK ADD CONSTRAINT [PK_tb_SM] PRIMARY
KEY CLUSTERED ([sm_pk])
INSERT INTO tb_tmp (sm_pk, sm_PublicText)
VALUES (@.id, '')
.......................................SET @.data here
--PRINT @.data ....................prints correctly
-- Save
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(4000), @.data)
WHERE sm_pk = @.id
As I said this works on my server BUT DOES NOT on the server of the client.
It leaves the row NULL. If I change the 4000 to 500 it works (obviously
truncating my records)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3F67A2FD-1B7B-40C2-AF32-4524C35961CA@.microsoft.com...
> Check the table definition, also look for any triggers.
>
> ML
> --
> http://milambda.blogspot.com/|||Try casting the value as the actual column's data type:
CONVERT(text, @.data)
ML
http://milambda.blogspot.com/

Row level trigger

Hi all

I am doing a DB porting project in which i need to translate oracle specific queries to sql server.I need to convert following trigger to sqlserver specific .Any help will be appreciated

CREATE OR REPLACE TRIGGER T_BI_R_TARGET_OBJECTIVE
BEFORE INSERT
ON TARGET_OBJECTIVE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (NEW.TARGET_OBJECTIVE_ID IN (NULL,0))
DECLARE
seq_id NUMBER;
BEGIN
SELECT TARGET_OBJECTIVE_ID_SEQ.NEXTVAL INTO seq_id FROM dual;
:new.TARGET_OBJECTIVE_ID := seq_id;
END;

Regards
sreenathFirst of all read thru SQL books online for TRIGGERS topic which explains the information on SQL Server.

HTH

Rounding problem with data conversion

Hi,
I am trying to convert a char column so that I can subtract the values from
another column. I have tried cast and convert, to change it to decimal, but
have found that both methods round values to the nearest whole number.
The column contains money, so this is causing me to lose the pence.
Any help would be greatly appreciated.
Many thanks
PaulI've managed to solve this now, so will close the thread.
The convert worked in the end.
Thanks anyway.
"PaulGodfrey" wrote:

> Hi,
> I am trying to convert a char column so that I can subtract the values fro
m
> another column. I have tried cast and convert, to change it to decimal, b
ut
> have found that both methods round values to the nearest whole number.
> The column contains money, so this is causing me to lose the pence.
> Any help would be greatly appreciated.
> Many thanks
> Paul

Rounding error

This should return 73.34...

However, it returns a 0 instead.

select convert(decimal(18,2),(3370)/(4595)*100)

Can you pl advise.

Either add a .0 to the end of the numbers, or explicitly convert to float.

The reason is that SQL Server, when dividing integers, returns an integer.

select convert(decimal(18,2),(3370.0)/(4595.0)*100.0) returns 73.34

BobP

|||

To add to Bob's comment:

IF both the dividend and divisor are whole numbers (int), SQL assumes you want the results as an int.

IF either of the two contains a decimal, a float will be returned. For example:

SELECT (( 3370./4595 ) * 100.0 )

--
73.3405000

|||

The datatype on the field, acctno is Int.. I use the convert statement to convert to float. but it still returns the data in INt ie it return 3370 instead of 3370.0.. Though I AM using covnert to float, data is still returned as an INT.

Select round(count(convert(float,convert(decimal(38,5),acctno))),2) from tbl1
WHERE (tbl1.balance<>0).

Any ideas how I can return The value as 3370.0

|||

Count returns an int... so there is no decimal.

Use convert(float, count(...

BobP

|||

Whoa nellie! What's with the round(), count() convert(), convert(), etc.

There is reason to convert(), round(), etc., it you are only interested in the count of rows meeting the criteria.

SELECT cast( count( AcctNo ) AS decimal(10,2))

FROM tbl1

WHERE tbl1.Balance <> 0

'should' do the trick.