|
I may or may not be responsible for my own actions
|
|
|
|
|
what are you trying to say
|
|
|
|
|
Your post makes very little sense. Can you try and better explain what you are trying to do as at the moment all you have is a list of poorly formatted values
I may or may not be responsible for my own actions
|
|
|
|
|
do you mean you want to group data?. if so, use <a href="http://www.w3schools.com/sql/sql_orderby.asp" target="_blank" >order by</a> .
Help people,so poeple can help you.
modified on Friday, March 11, 2011 2:16 PM
|
|
|
|
|
How can ORDER BY help in GROUPING data ? (I think you meant GROUP BY)
|
|
|
|
|
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;
|
|
|
|
|