Database

Re: helping writing sql query

20-Sep-10 21:05

I hate XML Dead | X|

but it is an excellent answer!

Never underestimate the power of human stupidity
RAH

i.j.russell20-Sep-10 21:18

i.j.russell

20-Sep-10 21:18

I find using STUFF to be slightly easier to read;

SELECT class,
  STUFF(
    (
    SELECT
      ',' + name
    FROM @tmp t2
    WHERE t2.class = t1.class
    FOR XML PATH('')
    ), 1, 1, '') AS data
FROM @tmp t1
GROUP BY class

Re: helping writing sql query

Mycroft Holmes21-Sep-10 3:49

21-Sep-10 3:49

I'd like to STUFF all xml and it's derivatives where the sun don't shine.

Never underestimate the power of human stupidity
RAH

complex sql query

abhi29198418-Sep-10 12:20

18-Sep-10 12:20

Hi All,

I am looking for query which needs two group by..

table looks like---

falconid priority affecteditem region
1 1 textbook ny
2 1 database ln
3 2 textbook ln
4 1 database zu
5 2 coin ny
6 2 textbook zu

now i want to result sorted by affected item and then count(*) according to region base in set like so that i can count how many ticket opened region vise..

affecteditem ln zu ny
textbook 2 1 0
database 1 1 0
coin 0 0 1

Thanks,
Abhishek

Andy_L_J18-Sep-10 13:07

Andy_L_J

18-Sep-10 13:07

Look at using a PIVOT

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Mycroft Holmes18-Sep-10 13:35

18-Sep-10 13:35

Assuming SQL Server, this article may help.

Pivot two or more columns in SQL Server 2005

Never underestimate the power of human stupidity
RAH

abhi29198419-Sep-10 0:23

19-Sep-10 0:23

Hi,

Apologize if you did not understand my problem..I am describing it again..

I have table which having four column,falconid,priority,affected item and region.
falconid is primary key,priority shows how much critical is problem,affected item shows which application is affecting and user is facing problem,region shows the different time zone
multiple user is raising the tickets from different zone with respect to affected item.that all will save in single table. like below..

falconid priority affecteditem region
1 1 textbook ny
2 1 database ln
3 2 textbook ln
4 1 database zu
5 2 coin ny
6 2 textbook zu

Now I want to calculate at the end of the day,that how many ticket(count*) has been raise for the particular affected item from which zone.. in table there are 3 ticket for textbook but from different region
I want to calculate the number of ticket raised from different region on affected item wise..like below

affecteditem ln zu ny
textbook 1 1 1
database 1 1 0
coin 0 0 1

Mycroft Holmes19-Sep-10 3:08

19-Sep-10 3:08

I understand, you want to PIVOT priority for each region by affecteditem. You need a PIVOT query, it is not a simple thing therefore I directed you to an ARTICLE that will help explain and guide you through creating the PIVOT query. There are also some excellent examples in BOL that you might want to work through.

I could write the query for you but I already know how to do these and you need to learn, so!

Never underestimate the power of human stupidity
RAH

abhi29198419-Sep-10 4:45

19-Sep-10 4:45

Hi Rah,

Thanks for the article,I am trying but as I am not the database resource so it will take the time.. I am learning now how pivote works.... but it is urgent for me as i have to demo of project tomarrow,, can u provide me the query on urgent basis.....

Thanks in advance,
Abhishek

abhi29198419-Sep-10 5:46

INNER, OUTER, LEFT, RIGHT JOINs- totally confused.

19-Sep-10 5:46

create table #table (affected item varchar, LN int, NY int, Zu int)

insert(table)

select

Affected item,SUM( case region when 'LN" then 1 else 0) LN,SUM( case region when "NY" then 1 else 0) NY,SUM( case region when 'ZU" then 1 else 0) ZU

from tablename groupby affected item

please advice if i am wrong?

Thanks,
Abhishek

lemarshall18-Sep-10 6:57

lemarshall

18-Sep-10 6:57

I rried to post a little bit ago but that must have bombed off. I'm using Access 2007 in query design mode

At this point I have gotten to where I show the 24 test records for Material Inventory in the correct storage yards. The SQL statement is:

SELECT MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID;

Now I need to show where two 2 items of material in each storage yard came from a DIFFERENT CostCenterID than the CostCenterID of the storage yard. The storage yards are costed based upon the lease they are loacted on. There can be several hundred wells for each lease- all with the same CostCenterID. SOMETIMES, material from one well on one lease (one CostCenterID) may be stored in another leases storage yard (another CostCenterID). I need to be able to track it and point it out.

So I add in the table Well and relate the fields Well.CostCenterID to MatInv.CostCenterID and I get 36000+ records. Not the original 24 test records. So obviously the Query designer isn't what I need so I am trying to manipulate things in SQL view- STILL without any luck. Trying to follow info on Access help that is just about useless. Current SQL statement is:

SELECT Well.CostCenterID, Well.Well, MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Well INNER JOIN (Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID) ON Well.CostCenterID = MatInv.CostCenterID;

Any assisance is appreciated,
Larry

Re: INNER, OUTER, LEFT, RIGHT JOINs- totally confused.

Mycroft Holmes18-Sep-10 13:32

Re: INNER, OUTER, LEFT, RIGHT JOINs- totally confused.

18-Sep-10 13:32

This article may help.

Caveat - I use SQL Server not Access
I find it easier to lay out my own joins so they are more human readable. Start with the minimum tables to get the minimum result, then expand on it.

Get a list of material and their storage location with cost center
Get a list of wells and their cost centres
get the relationship between well and material, compare cost centers

Never underestimate the power of human stupidity
RAH

lemarshall19-Sep-10 12:37

lemarshall

19-Sep-10 12:37

I liked the link- useful. Right on, I had started back with just two tables and worked my way up from there. I finally decided to add another ID field to one of the tables (breaking normalcy rules) but it helped make it an easier sql select statement. I only had to change a small bit of code to make sure that everything gets saved properly to the tables. So it works now- thanks.
Larry

Re: INNER, OUTER, LEFT, RIGHT JOINs- totally confused.

Mycroft Holmes19-Sep-10 12:44

Sql queries on unicode data [modified]

19-Sep-10 12:44

lemarshall wrote:
(breaking normalcy rules)

These should be known as guidlines as they are subject to reality and business requirement. Having said that I alway looks very closely at a design that does not conform to these guidelines, it is usually wrong somewhere.

You got it fixed - always satisfying.

Never underestimate the power of human stupidity
RAH

shiva.kore17-Sep-10 21:19

shiva.kore

17-Sep-10 21:19

hi,

i have created database with some fields containing nvarchar data. updated data in unicode kannda language text. I am writing queries like :- select * from table where district='UÀÄ®âUÁð'.It will not retriewing any rows. command executed success.

Thanks

************ S G KORE *******************

-- Modified Saturday, September 18, 2010 3:26 AM

Re: Sql queries on unicode data

Rob Smiley17-Sep-10 23:57

Rob Smiley

17-Sep-10 23:57

try: select * from table where district=N'UÀÄ®âUÁð'

nvarchar and ntext types require the N prefix before the opening quote when you're searching for unicode text.

"An eye for an eye only ends up making the whole world blind"

Re: Sql queries on unicode data

shiva.kore19-Sep-10 19:07

shiva.kore

19-Sep-10 19:07

Thank you Rob Smiley.

Its working

************ S G KORE *******************

Ordered selection of 2 columns in single SQL query

Emmet_Brown17-Sep-10 19:44

Emmet_Brown

17-Sep-10 19:44

Hey,

I have a table named volleyballsecondround8 which has columns like 'Winner', 'Loser' and 'MatchNo'.

I'd like to make a SELECT statement as it takes the 'Winner' where 'MatchNo'='M20' and puts it ind the [0]. row of my new table, then takes the 'Loser' of 'MatchNo'='M20' and puts it into the second row.

Therefore;

When I execute a query on volleyballsecondround8 like the following:

Select @row := @row + 1 as row,Winner,Loser
from volleyballsecondround8,(SELECT @row := 0) r
Where MatchNo IN('M20','M19','M18','M17') Order

I get e resultant table like:

ROW-Winner-Loser
----------------
1-India-Luxemburg
2-USA-Spain
3-Turkey-Argentina

Where, I want a table like:

ROW-TEAM
-----------
1-India
2-Luxemburg
3-USA
4-Spain
5-Turkey
6-Argentina

any help?

Re: Ordered selection of 2 columns in single SQL query

i.j.russell17-Sep-10 22:35

i.j.russell

17-Sep-10 22:35

SELECT Row,
	0 AS resultorder,
	Winner
FROM volleyballsecondround8
UNION ALL
SELECT Row,
	1,
	Loser
FROM volleyballsecondround8
ORDER BY Row ASC, resultorder ASC

Re: Ordered selection of 2 columns in single SQL query

Emmet_Brown18-Sep-10 0:28

Emmet_Brown

18-Sep-10 0:28

thanks for help but I decided to take all the data to a DataTable and then sort it in the C# part of the project, the hard way =)

thanks again

How do I insert escape sequences using SQL Compact.

Spawn@Melmac17-Sep-10 3:32

Spawn@Melmac

17-Sep-10 3:32

Simple question I know but I just cannot find a anything that works.

INSERT INTO [Location] ([id],[TZOffset],[Address],[Tel],[Fax],[Contact1],[Contact2],[Contact3],[Company],[Deleted],[Disabled]) VALUES (8,null,N'line1
line2
',N'',N'',1,2,3,null,null,null);
GO

I have tried everything I can find but there is no ESCAPE or CHAR function in SQLCE that I can find. You cannot use Stored Procedures, being CE so it has to be done in a command space.

If I store the CRLF in the raw data and push it using MFC then it works fine.

Any ideas?

Great isn't it. I can use SIN, COT, SQRT but I cannot put CRLF into a string!!?!??!?!

Alan

Re: How do I insert escape sequences using SQL Compact.

Mycroft Holmes17-Sep-10 4:53

Re: How do I insert escape sequences using SQL Compact.

17-Sep-10 4:53

Me thinks your data structure is screwed, whenever I seelsomething like contact1, contact2, contact3 I know you are in deep poop. What if there is a fourth contact, woops lets add another column - FAIL

Create another table callemd contacts and a foreign key to this company table.

What has CRLF got to do with your insert statement?

There seems to be a number od problems with your insert statement. There is no comma between line1 and line2. Do comapny, deleted and disabled accept null as valid data.

Never underestimate the power of human stupidity
RAH

Spawn@Melmac17-Sep-10 6:04

Spawn@Melmac

17-Sep-10 6:04

Well you would be right if that is the way the fields were being used but they are not. There will only be three contacts as these fields hold pointers to entries in a Contact table.

I want to use a single ADDRESS field to store a multiline address rather than have 5 separate fields (one for each line).
There is no comma between line1 & line2 becuase they are stored as "line1\r\nline2" in the application internals.

Now I can do that everywhere else (CString, Registry etc...) so why is it so unreasonable to want the same thing in SQL CE?

Alan

Re: How do I insert escape sequences using SQL Compact.

Mycroft Holmes17-Sep-10 13:47