|
|
Over my head, mika;
you are still the best.
100
Help people,so poeple can help you.
|
|
|
|
|
Thanks, but frankly I believe that this site has a bunch of real experts thus making CP so special. No other site I've come across has so positive and professional attitude at the same time as this.
|
|
|
|
|
CREATE TABLE #data
(
ID INT NOT NULL,
PID INT NULL,
VALUE VARCHAR(20) NOT NULL
);
INSERT INTO #data
(ID, PID, VALUE)
VALUES
(1, NULL, 'node 1'),
(4, 1, 'node 1/1'),
(6, 4, 'node 1/1/1'),
(5, 1, 'node 1/2'),
(7, 1, 'node 1/3'),
(2, NULL, 'node 2'),
(3, NULL, 'node 3'),
(8, 3, 'node 3/1');
WITH MyCTE AS
(
SELECT ID, PID, VALUE, 0 AS [LEVEL]
FROM #data
WHERE PID IS NULL
UNION ALL
SELECT D.ID, D.PID, D.VALUE, [LEVEL] + 1
FROM MyCTE M
JOIN #data D
ON D.PID = M.ID
)
SELECT ID, PID, VALUE, [LEVEL]
FROM MyCTE;
DROP TABLE #data;
|
|
|
|
|
thank you Russell.
100
|
|
|
|
|
About me adam763 aadams
CaringIn Action adam763 aadams
LocalBusiness adam763 aadams
From My Desk adam763 aadams
About me agga924 aaggarwal
From My Desk agga924 aaggarwal
LocalBusiness agga924 aaggarwal
About me aldr231 aaldridge
From My Desk aldr231 aaldridge
About me alkh290 aalkhazshvilly
LocalBusiness alkh290 aalkhazshvilly
From My Desk alkh290 aalkhazshvilly
About me alti800 aaltis
From My Desk alti800 aaltis
About me arev258 aarevalo
From My Desk arev258 aarevalo
About me arri922 aarriaga1
From My Desk arri922 aarriaga1
About me bakk607 abakken
bout me bart768 abarton
From My Desk bart768 abarton
i need the data to be present as
in the first column repeated text is there
for suppose Title TitleCount
About Me 23
after this i have to display
Title UserName Usersite
About Me barttt xxxxxx
About Me xxxx xxxxx
About Me xxxx xxxxx
|
|
|
|
|
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
|
|
|
|
|