Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
ABC
1ax
2by
3c1
4nullnull
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1sql

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53B523BC4BB04@.SQLMonster.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1

Distinct at two columns

I have this stored procedure:

ALTER PROCEDURE usp_My_Procedure
(
@.Country varchar(5)
)

AS
SELECT DISTINCT City, Short FROM Table1 WHERE Country = @.Country

RETURN

I want to select just one of each 'city' and 'short' in the database...But this is not working correct.....Whats wrong?

Lets say that I have a table that looks something like this

City Short

New York NY

Los Angeles LA

Lake Alice LA

Los Angeles LosAng

well ur code like this is anylized like give everything not repeated for these two columns,

try this

SELECT City, Short FROM Table1 WHERE Country = @.Country

and City in (SELECT DISTINCT City FROM Table1 WHERE Country = @.Country)

and Short in (SELECT DISTINCT short FROM Table1 WHERE Country = @.Country)

sql

Displaying Small Money

Hi,
How do I remove the decimal places from this to show just whole pounds
?
SELECT '=A3' + convert(varchar(30),PotentialRevenue,3) as [Potential
Revenue]
What is the purpose of the small money data type ? - its seems to have
no features for storing/displaying financial data that cant be done
with other data types.> How do I remove the decimal places from this to show just whole pounds?
The formatting of numeric values is controlled by your client
application, not by SQL Server and that's how it should be. ROUND the
value on the server if you need to but do presentation client-side.

> What is the purpose of the small money data type ?
Good question. In some cases the money types may save you 1 byte over a
DECIMAL column. However, given the rounding errors caused when you
divide or multiply the money types I would always avoid them unless
compelled to use them. I can't think of a good reason to use MONEY or
SMALLMONEY.
David Portas
SQL Server MVP
--|||> DECIMAL column. However, given the rounding errors caused when you
> divide or multiply the money types I would always avoid them unless
> compelled to use them. I can't think of a good reason to use MONEY or
> SMALLMONEY.
Nor can I.
For the OP's benefit: http://www.aspfaq.com/2503

Displaying Chinese Character in varchar columns

I would like to store some data in Chinese Big5 using a varchar column. However, all the data turn in to some garbage characters like Ru￥Uao? .
I ran into no problem if i use nvarchar but I would like to use varchar because I can only store less than 4000 characters using nvarchar.

It works if I paste the characters directly into the SQL Express Client. It only turns into some garbage characters if i use C++ to insert the data.

I am using Chinese_Taiwan_Stroke_BIN collation (The chinese won't show if I use the default latin collation) on the column and my computer regional setting for non-unicode application is Traditional Chinese(Taiwan).

Any ideas how can I fix this problem? I am suspecting there's a problem with my computer setting?

Thanks!

If you need more than 4000 characters, you should consider using an NText column type. A standard varchar column can only hold characters from its native character set.|||but then ntext insertion and retrival time would be slower and we don't want that

Display subtotals and grand total

Hi,

I have a table:

CREATE TABLE [dbo].[TBL_REPORT1](

[Source] [varchar](3) NULL,

[Contract No] [varchar](15) NOT NULL,

[Business Group] [varchar](4) NULL,

[Customer Name] [varchar](50) NULL,

[Equipment Description] [varchar](20) NULL,

[Lease Type] [varchar](2) NULL,

[Term] [int] NULL,

[Booking Date] [datetime] NULL,

[# of Assets] [int] NULL,

[Equipment Cost] [money] NULL,

[Restructured] [varchar](3) NOT NULL

)

Sample Data

INSERT INTO [TBL_REPORT1] VALUES('SFS','319-0010146-001','SEF','NorthBay Healthcare Group','SBT Performance Cont','LP',132,'Apr 4 2007 12:00:00:000AM',1,2612000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015625-023','SEF','Black Diamond Properties, Inc.','Kubota L48 TLB Tract','OL',60,'Apr 3 2007 12:00:00:000AM',1,36000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015648-007','SEF','The River Wilderness Club, Inc.','Honda Salsco Greens','OL',48,'Apr 5 2007 12:00:00:000AM',1,11401.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0013599-020','VEN','THYSSENKRUPP BUDD COMPANY','COMPUTER GEAR','CS',30,'Apr 5 2007 12:00:00:000AM',1,232965.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0016965-002','VEN','GREEN OAK TOWNSHIP','COMPUTER GEAR','CS',33,'Apr 5 2007 12:00:00:000AM',1,56789.9100,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0052401-001','VEN','Zircon Corp.','INJECTION MOLDING MC','CS',70,'Apr 11 2007 12:00:00:000AM',1,74380.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','766-0001804-007','IGP','Helena Chemical Company','1800 GAL','TL',36,'Apr 18 2007 12:00:00:000AM',17,292147.7000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-001','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,276928.4500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-002','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,377415.3500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002053-001','CBF','VIH Helicopters USA, Inc.','Sikorsky S-61N','CS',84,'Apr 6 2007 12:00:00:000AM',1,4612500.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0014680-024','CPM','SUN MICROSYSTEMS, INC.','trade receivable','CS',2,'Apr 5 2007 12:00:00:000AM',1,20177632.2300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0015956-014','CPM','Autozone Inc.','trade receivable','CS',11,'Apr 3 2007 12:00:00:000AM',1,2128173.3600,'No')

I want to display subtotals - sum of [Equipment Cost] for each [Business Group] and also the grand total.

Sample Output:

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Count 3 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Count 2

I tried with CUBE and ROLLUP but then with multiple fields it was not giving me the right output. Can anyone help. Thanks.

Posted above was the sample count subtotal generated in Excel. I am interested in getting only the subtotal count/sum and the grand total. Labels like CBF Count, CPM Count and not required.

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Total 5266844 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Total 22305806 SFS 766-0001804-007 IGP Helena Chemical Company 1800 GAL TL 36 00:00.0 17 292147.7 No IGP Total 292147.7 Grand Total 27864797

|||

If using SQL Server 2005, have you looked at COMPUTE?

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

Dan

|||Thanks.|||I hope it does what you need!

display string having length greater than 255

I have a created a table and entered data into the table as follows:

CREATE TABLE t ( id INT , txtcol varchar(1000) )

INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'ATXR_SOURCE_ID,CDDL_AG_PRICE,CDDL_ALLOW,CDDL_ALTDP_EXCD_ID,CDDL_CAP_IND,CDDL_CHG_AMT,CDDL_COINS_AMT,CDDL_CONSIDER_CHG,CDDL_COPAY_AMT,CDDL_DED_AC_NO,CDDL_DED_AMT,CDDL_DIS_PA_LIAB,CDDL_DISALL_AMT,CDDL_DISALL_EXCD,CDDL_DISC_AMT,CDDL_DP_PRICE,CDDL_FROM_DT,CDDL_PAID_AMT,CDDL_PF_PRICE,CDDL_PR_PYMT_AMT,CDDL_PRICE_IND,CDDL_REF_IND,CDDL_RISK_WH_AMT,CDDL_SB_PYMT_AMT,CDDL_SURF,CDDL_TOOTH_BEG,CDDL_TOOTH_END,CDDL_TOOTH_NO,CDDL_TOT_PA_LIAB,CDDL_UNITS,CDDL_UNITS_ALLOW,CGCG_ID,CGCG_RULE,DPCG_DP_ID_ALT,DPDP_ID,DPTC_CD,PDVC_LOBD_PTR,PSDC_ID,UTUT_CD' )

Now if i select data using the query below the txtcol field displays only 255 characters :

SELECT * FROM t

Y is this happening?

This is happening because of your client application (QA, SSMS or something else) has a setting not to display field values grater than 255 bytes. Inspect application options, then.

Display over 256 characters for VARCHAR(3500) field

Hopefully, someone can help me.
I am working with a database that contains multiple fields within the tables that are being used for Clinical notes. The fields are defined as VARCHAR(3500). But when I try to extract data (either through Query Analyzer or Crystal Reports), only the first 256 characters are displayed. I ran a query to give me the length of the maximum entry size which returned 2722 characters, yet only 256 are displayed.

How do I go about extracting ALL of the data from this field? Any help is much appreciated.

Thanks in advance.In Query Analyzer, select Options from the menu and bump up the maximum output characters, which defaults to 256.

Display ONLY Month, year from SQL2k

Tried this:

SELECT CONVERT(varchar,fieldMonthYear,107) 'Month in Question' FROM ....

That returns: Apr 01, 2006
What I need is this: April 2006 or even Apr 2006. But no date for the day.

Is there a way I can trim the center 4 characters of this now converted varchar? This is in a datalist, btw. Thanks!

bs.

SELECT Left(CONVERT(varchar,getdate(),107), 4) + RIGHT(CONVERT(varchar,getdate(),107), 4) AS 'Month in Question'|||

Thank you.

while you gave an acceptable answer, I found another way - I'll still mark your answer as right because it answered my question originally.

This is what I used to get it to work:

SELECT CONVERT(VARCHAR(10), DATENAME(MM,fieldMonthYear)) + ', ' + CONVERT(VARCHAR(4), Year(fieldMonthYear)) AS 'Month in Question' FROM tblTableName

God bless...
iSheahan

|||

There is a better way to format datetime from client side.

<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource3">
<ItemTemplate>

LastActivityDate:
<asp:Label ID="DateLabel" runat="server" Text=' <%# DataBinder.Eval(Container.DataItem, "fieldMonthYear","{0:MMM yyyy}") %>'> </asp:Label>

</ItemTemplate>

</asp:DataList>

Or simply: <asp:Label ID="Label1" runat="server" Text=' <%# Eval("fieldMonthYear","{0:MMM yyyy}") %>'> </asp:Label>

Enjoy.