Database

8-Oct-09 20:20

Thank you

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

SQL Query Problem

Syed Shahid Hussain7-Oct-09 4:29

7-Oct-09 4:29

hi
I want to make a query for this data can any one of u help me;

DATA:


Person       Name         value
XYZ          a            10
XYZ          b            5 
XYZ          a            15
XYZ          b            4
XYZ          c            1
XYZ          a            5
XYZ          d            10
XYZ          a            10
...
...

This is the of one group;
now for I want this result for this data and so on;


Person      Name         Total
XYZ         a            40
XYZ         others       20
abc         a            ##
abc         others       ##

Please write a query for this.

Thanks Rose | [Rose]

Syed Shahid Hussain

dan!sh 7-Oct-09 4:53

dan!sh

7-Oct-09 4:53

Syed Shahid Hussain wrote:
Please write a query for this.

Why?

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

Syed Shahid Hussain7-Oct-09 5:30

7-Oct-09 5:30

very stupid,

if u dont know then say "i dont know sorry" what do u mean by WHY?

Syed Shahid Hussain

dan!sh 7-Oct-09 5:44

dan!sh

7-Oct-09 5:44

Syed Shahid Hussain wrote:
if u dont know then say "i dont know sorry"

Instead, I do not reply if I do not know anything about the question.

Syed Shahid Hussain wrote:
what do u mean by WHY?

Is this a quiz website? No.
Why should I do your work? If you had tried something and got stuck, then many would be interested to help you.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

Syed Shahid Hussain7-Oct-09 5:50

7-Oct-09 5:50

d@nish wrote:
Is this a quiz website?

brother it is a forum I think u dont know the meaning of forum, here people help each other without Why? and without any thing. I'd solved my problem but I want some suggestions from others.

Now if u know any idea about that then reply otherwise keep quiet

thank you very much.

Syed Shahid Hussain

dan!sh 7-Oct-09 5:55

dan!sh

7-Oct-09 5:55

People here help those who help themselves. People here do not prefer spoon feeding in general. Try out something or search google. If you face any problems, then post the query.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

Syed Shahid Hussain7-Oct-09 6:04

7-Oct-09 6:04

I've said that I've solved the problem but I want some perfect solution from the experts, why are u arguing me, and who has given u this right to argue with me.

Syed Shahid Hussain

Luc Pattyn7-Oct-09 8:45

Luc Pattyn

7-Oct-09 8:45

Syed Shahid Hussain wrote:
I've solved the problem

Then I suggest you show your solution and ask if anyone sees ways to improve on it.
That way typically leads to a more interesting thread.

Smile | :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Local announcement (Antwerp region): Lange Wapper? Neen!

Syed Shahid Hussain7-Oct-09 10:43

7-Oct-09 10:43

leave it dear,

now first time i'm irritating with this site, code project. Every time when i post a question i received a perfect solution or at least pleasant comments not like these stupid phrases. One want 3 hr to solve it and i'd given only 2 hr to solve it and make related crystal report with complete formatting.

now just stop posting replies on this question.

Syed Shahid Hussain

J4amieC7-Oct-09 6:44

7-Oct-09 6:44

Syed Shahid Hussain wrote:
Please write a query for this.

Only because you used "Please" am I replying to this.

This may be a forum where people help each other, but it is NOT a place where professionals will do your work for you. Most professionals charge for professional work.

Instead, we help people who are stuck on a problem. A pre-requisite to getting help is showing that you are able to at least have a stab at doing it yourself, and therefore when you hit a specific problem, people here are more than happy to help.

But just saying "Please do x for me" will get you either the reply you've gotton above, or a quotation.

I can do this work for you at £50 + VAT per hour and I imagine with implementation and testing there is 3 hours work there.

mnatarajakumaran7-Oct-09 18:23

mnatarajakumaran

7-Oct-09 18:23

try this,

select person , name, sum(tot_values) from
( select person,
case when name = 'a' then 'a'
else 'others'
end as name , sum(tot_values) as tot_values from test_tabb
group by person,name
) X
group by person,name
order by person desc, name

J4amieC7-Oct-09 23:03

7-Oct-09 23:03

Why would I want to try that? I didnt ask how to do it in the first place!

Besides, theres a far simpler way with about half as many lines. (Hint: You can user a case statement in a group by clause Wink | ;)

)

Niladri_Biswas27-Oct-09 18:24

Niladri_Biswas

27-Oct-09 18:24

Try this

declare @tbl table(person varchar(50),name varchar(50),value int)
insert into @tbl
select 'xyz','a',10 union all select 'xyz','b',5 union all
select 'xyz','a',15 union all select 'xyz','b',4 union all
select 'xyz','c',1 union all select 'xyz','a',5 union all
select 'xyz','d',10 union all select 'xyz','a',10 union all
select 'abc','a',0 union all select 'abc','b',0 union all
select 'abc','c',0 union all select 'abc','d',0

;with cte as
(
select person,name,sum(value) as total from @tbl group by person,name
)

select person,'others' as name , SUM(total) as total from cte where name not in('a') 
group by person
union all
select person,'a' as name , SUM(total) as total from cte where name in('a') 
group by person
 order by person desc

Niladri Biswas

Bulk Import Error

Vimalsoft(Pty) Ltd6-Oct-09 23:35

6-Oct-09 23:35

Good Day All

i have a Following Query that does the Bulk insert

BULK INSERT dbo.TBL_CMPS FROM C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv  WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );

i get an Error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'C'.

and when i add the Quoates in the path like this

BULK INSERT dbo.TBL_CMPS FROM 'C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv'  WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );

i get the Following Error

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).

Thank you

Eddy Vluggen6-Oct-09 23:49

Eddy Vluggen

6-Oct-09 23:49

This might work;

BULK INSERT dbo.TBL_CMPS FROM 'C:\UNISA_IMPORT\Final_Import\Campuses.csv'  WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );

The first one doesn't work because of the quotes, the second one has escape-characters in the path.

I are Troll Smile | :)

Vimalsoft(Pty) Ltd7-Oct-09 0:04

7-Oct-09 0:04

Good Day Eddy

The Error is still there

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\UNISA_IMPORT\Final_Import\Campuses.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).

ASP, Admin, Everyone , has read and Write access to this File

J4amieC6-Oct-09 23:54

6-Oct-09 23:54

Operating system error code 3(The system cannot find the path specified.)

These things don't tend to lie, however they can be misleading.

Are you sure the user running this process has access to this file? If so, why are you sure? Are you doubly sure? If so, are you triplly sure the path is correct?

Vimalsoft(Pty) Ltd7-Oct-09 0:09

7-Oct-09 0:09

yes , this is Happening in my Development machine and i have local admin Rights, the ASP account has access to this File and Everyone has access to this File.

Thanks

Vimalsoft(Pty) Ltd7-Oct-09 0:48

7-Oct-09 0:48

The Problem is that the SQL Server is in another Machine and am in my Developement Machine. So the Path is not in the Machine. the Solution is to Add the File to the Server and after that access the File after am done with the Import and Delete the File. Now i have one Question. in my database table i have an Identity Column

BULK INSERT dbo.TBL_CMPS FROM 'C:\UNISA_IMPORT\Final_Import\Campuses.csv'  WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );

How can i make the second Column as the First one. Because it tries to add records in the identity field and i get the Following Errors

ulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (ID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (ID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (ID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 1 (ID).
Msg 4864, Level 16, State 1, Line 1
Bu

Thank you

J4amieC7-Oct-09 0:58