January 4, 2012 at 4:36 am
Hi all,
here is my table data
ID PID Name
1 0 Ad
2 0 Area
3 1 List
4 1 Detail
5 2 List
6 2 Detail
Now i want to display data in this formate
Ad List
Ad Detail
Area List
Area Detail
how ll be select query?
January 4, 2012 at 4:54 am
Here's a demo with the system tables, adjust to your own needs.
SELECT
T.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
January 4, 2012 at 4:58 am
Or a more simplistic approach...
-- *** Test Data ***
CREATE TABLE #t
(
ID int NOT NULL
,PID int NOT NULL
,[Name] varchar(25) NOT NULL
)
INSERT INTO #t
SELECT 1, 0, 'Ad'
UNION ALL SELECT 2, 0, 'Area'
UNION ALL SELECT 3, 1, 'List'
UNION ALL SELECT 4, 1, 'Detail'
UNION ALL SELECT 5, 2, 'List'
UNION ALL SELECT 6, 2, 'Detail'
-- *** End Test Data ***
SELECT T1.[Name], T2.[Name]
FROM #t T1
JOIN #t T2
ON T2.PID = T1.ID
WHERE T1.PID = 0
ORDER BY T1.ID, T2.ID
January 4, 2012 at 5:01 am
Ok, what do you do with let's say a max of 16 rows per group?
January 4, 2012 at 5:14 am
engrshafiq4 (1/4/2012)
@Ninja's_RGR'us:Your suggestion is very complecated for me...let me try @ken McKelvey comment
Break it down 1 step at a time in your mind. Takes a little work but it's simple once you understand it ;-).
As I said, the other proposed solution doesn't work as soon as you have more than 2 rows per group. It quickly becomes unmanagable by using joins only.
January 4, 2012 at 5:19 am
Ninja's_RGR'us (1/4/2012)
Here's a demo with the system tables, adjust to your own needs.
SELECTT.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
yes but i do not know FOR
XML PATH('') and things like ','....can you give it for my table structure?
January 4, 2012 at 5:23 am
No because you need to understand the code to be able to debug it & adjust for more needs.
XML just format as xml (this is where the concatenation happens).
Stuff trims.
Use this code instead and see how it changes the output.
',' + name AS WhatchThis
January 4, 2012 at 5:32 am
i know that sys.tables is Mytable name but what is sys.columns C
WHERE C.object_id = T.object_id
is this sys.columns mytable name?
January 4, 2012 at 5:39 am
In that case yes.
For performance sake I'd pre-distinct the ids so that you don't concatenate n times too many for no reasons.
Something like this :
;
WITH CTE (object_id)
AS
(
SELECT DISTINCT object_id FROM sys.columns
)
SELECT
T.object_id,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM CTE T
ORDER BY object_id
January 4, 2012 at 5:44 am
If sys.columns is table name(as i have single table)...then i do have two ids,one is Id and other is PID(parentID).so what do you mean by object_id?please differentiate PromeryKey Id which is Id and parentId which is PID?
January 4, 2012 at 5:46 am
That's what you need to understand ;-).
Only 2 options, not too hard to test this out on your own. :hehe:
January 4, 2012 at 5:54 am
WITH tbl_be_modules (parentid)
AS
(
SELECT DISTINCT parentid FROM tbl_be_modules
)
SELECT
T.Id,
STUFF((
SELECT
',' + displayname
FROM
tbl_be_modules C
WHERE C.id = T.parentid
ORDER BY
displayname
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM tbl_be_modules T
ORDER BY id
this code give me error
Recursive common table expression 'tbl_be_modules' does not contain a top-level UNION ALL operator.
and
SELECT
T.Id,
STUFF((
SELECT
',' + displayname
FROM
tbl_be_modules C
WHERE C.id = T.parentid
ORDER BY
displayname
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM tbl_be_modules T
ORDER BY id give me unwanted result
January 4, 2012 at 6:00 am
This looks like hierarchical or "tree structured" data.
The following query can be used to extract hierarchical or "tree structured" data.
/** setup data **/
declare @table table (ID int, PID int, Name varchar(100))
insert into @table values (1,0,'Ad')
insert into @table values (2,0,'Area')
insert into @table values (3,1,'List')
insert into @table values (4,1,'Detail')
insert into @table values (5,2,'List')
insert into @table values (6,2,'Detail');
--select * from @table;
/** recursive tree query **/
with Tree (ID, PID, Name, TreeLevel, ConcatName)
as
(
select
ID,
PID,
Name,
TreeLevel = 1,
ConcatName = cast(Name as varchar(100))
from
@table
where
PID = 0
union all
select
t.ID,
t.PID,
t.Name,
TreeLevel = Tree.TreeLevel + 1,
ConcatName = cast((Tree.name + ' ' + t.Name) as varchar(100))
from
@table t
inner join
Tree on t.PID = Tree.ID
)
select
*
from
Tree
where
TreeLevel > 1
order by
ConcatName asc
January 4, 2012 at 6:01 am
My bad, I misunderstood the requirements. The code I gave you is for concatenation.
How many levels deep do you need to go? If it's always 1 then the query from the other poster should be fine.
If you need more than that I'll call in help as I've never done that.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply