|
or group by, but why to use it scinse there is no need to use aggregate functhins.
in addition order by also sort data
100
Help people,so poeple can help you.
|
|
|
|
|
Of course he need a aggregate function, for the title count ...
|
|
|
|
|
So, you are the one with the answer below. ok i admit that i misread.
shall you forgive my mistake!
100
Help people,so poeple can help you.
|
|
|
|
|
I'll forgive you )))
Just wondered that you said 'grouping items' and than advise 'order by' - that confused me
Anyway, I'm glad to be able to share your community !
Regards,
kwk
|
|
|
|
|
Klaus-Werner Konrad wrote: that confused me
you shouldn't be (i am not familiar with database)
100
Help people,so poeple can help you.
|
|
|
|
|
My be this will work (in SqlBase it works):
SELECT Title, TO_CHAR( COUNT(*), 0), NULL FROM Table GRUOP BY Title
UNION
SELECT Title, UserName, UserSite FROM Table
ORDER BY 1, 2, 3
|
|
|
|
|
As they say "Think twice before you leap !".
Have a 5
100
Help people,so poeple can help you.
|
|
|
|
|
Hi
My sql query result is
Store month sale purchase
A Jan 2000 150
I want my result like
Store month Expense value
A Jan Sale 2000
A Jan Purchase 150
Please help me how to get this result using sql query.
Thanks in advance
|
|
|
|
|
Use 2 queries with a UNION between them.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Could you tell me the approach of using 2 queries with a UNION.
Or tell me the syntex how to apply union to get the result.
Thanks
|
|
|
|
|
Seriously - you do not have BOL or google where you are!
Select House, 'Sale', Sale as Value
from Table
union
Select House, 'Purchase', Purchase as Value
from Table
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
You can use a union, as already suggested, and with SQL Server you can also use the UNPIVOT command.
WITH myData(Store , Mth , Sale , Purchase )
AS
(
SELECT 'A','Jan', 2000,150
UNION SELECT 'B','Jan', 1000,200
)
SELECT Store, Mth, Expense, Value
FROM
(
Select Store, Mth, Sale, Purchase
FROM myData
) p
UNPIVOT
( Value FOR Expense IN (Sale, Purchase) ) AS unpvt;
Let me explain that a bit. The first bit (Starting 'WITH') just gives us some example data, I have added a second row onto your original data to help confirm the result.
The second bit (Starting 'SELECT') Selects the data from an unpivoted view of your SALE and Purchase columns. The result of the above query is
A Jan Sale 2000
A Jan Purchase 150
B Jan Sale 1000
B Jan Purchase 200
|
|
|
|
|
Hi,
How do I write the SQL code to select data from these tables:
tblPerson
ID DOB
1 01/01/2010
2 01/02/2011
3 10/01/2009
tblPersonName
ID FName MName LName NameType
1 James D Doe L
1 Jim (null) Doe C
2 Martha (null) Stu L
3 William H Jefferson L
3 Bill (null) Jefferson
to look like this (flattened)?
ID FName_Legal MName_Legal LName_Legal FName_Common MName_Common LName_Common
1 James D Doe Jim (null) Doe
2 Martha (null) Stu (null) (null) (null)
3 William H Jefferson Bill (null) Jefferson
Thanks
|
|
|
|
|
The column headers seem like you want to select the person with the same first, middle OR last name but your results seem like you would select only based on the same last name. However, basically you would use self join. If the last name is used to match rows, it could be something like:
SELECT a.*, b.*
FROM tblPersonName a LEF OUTER JOIN tblPersonName b
ON a.lname = b.lname
This query has problems though (depending on your specs). If you have three persons with the same last name you get those on multiple rows (each pair). Also each pair will be listed twice (both ways), but I'm not able to say if that's what you want.
Also the ID field seems quite suspicious. Do you really have several rows with the same id? If not, that could be used to eliminate the pairs from being listed twice (again if that's what you want).
|
|
|
|
|
Hi Mika,
Sorry my example wasn't clear.. ID is the PK for tblPerson and a FK in tblPersonName. The join will be done using the ID field. tblPersonName contains a table of all the different names a person has e.g. Legal name, common name etc
What I want to do is to be able to list all the different names of a person in one row as opposed to multiple rows.
Thanks
|
|
|
|
|
|
|
|
CREATE TABLE #Person
(
ID INT NOT NULL,
DOB DATE NOT NULL
);
CREATE TABLE #PersonName
(
ID INT NOT NULL,
PID INT NOT NULL,
FNAME VARCHAR(50) NOT NULL,
MNAME VARCHAR(10) NULL,
LNAME VARCHAR(50) NOT NULL,
NAMETYPE CHAR(1) NOT NULL
);
INSERT INTO #Person
(ID, DOB)
VALUES
(1, '2010-01-01'),
(2, '2011-02-01'),
(3, '2009-01-10');
INSERT INTO #PersonName
(ID, PID, FNAME, MNAME, LNAME, NAMETYPE)
VALUES
(1, 1, 'James', 'D', 'Doe', 'L'),
(2, 1, 'Jim', NULL, 'Doe', 'C'),
(3, 2, 'Martha', NULL, 'Stu', 'L'),
(4, 3, 'William', 'H', 'Jefferson', 'L'),
(5, 3, 'Bill', NULL, 'Jefferson', 'C');
WITH L AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'L'
),
C AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'C'
)
SELECT P.ID,
L.FNAME AS FNAME_L,
L.MNAME AS MNAME_L,
L.LNAME AS LNAME_L,
C.FNAME AS FNAME_C,
C.MNAME AS MNAME_C,
C.LNAME AS LNAME_C
FROM #Person P
LEFT JOIN L
ON L.PID = P.ID
LEFT JOIN C
ON C.PID = P.ID;
DROP TABLE #PersonName;
DROP TABLE #Person;
|
|
|
|
|
|
Hi,
I want to create a report with 3 different datasets (dataset 1, 2 and 3) from a single datasource. The report layout will be a 3 section for every single record found in dataset1. Dataset 2 and dataset 3 will get the ID from dataset1 and display the 2nd and 3rd section of the report. Please help me how to create this kind of report.
Thanks
|
|
|
|
|
Are you talking about Gupta Report Builder ?
|
|
|
|
|
Hi,
I would like to install the sql server 2005 express on my machine.
I have found a download for it but it does not tell me if it includes the management studio (The window where I can see databases, tables, etc...)
Is there another download for just the management studio of the sql; server 2005 express?
Thanks
|
|
|
|
|
|