December 18, 2012 at 9:51 am
hello, i am providing these small example to make myself better understood.
CREATE TABLE #Groups
(
group_id bigint NOT NULL,
name varchar(100) NOT NULL,
grouptype_id bigint NOT NULL,
PRIMARY KEY (group_id)
);
CREATE TABLE #GroupTypes
(
grouptype_id bigint NOT NULL,
name varchar(30) NOT NULL,
PRIMARy KEY (grouptype_id)
);
go
insert into #Groups values (1, 'Group 1', 1);
insert into #Groups values (2, 'Group 2', 2);
insert into #Groups values (3, 'Group 3', 1);
insert into #Groups values (4, 'Group 4', 2);
insert into #GroupTypes values (1, 'Type 1');
insert into #GroupTypes values (2, 'Type 2');
-- I don't know if this is a good idea yet, but i would like to fill a template where
-- the values were inserted like these:
-- Group Name Type 1 Type 2
-- Group1 X -
-- Group2 - X
-- Group3 X -
-- Group4 - X
drop table #Groups
drop table #GroupTypes
good idea, bad idea ? how can i manage to do this ?
December 18, 2012 at 10:10 am
If you're 100% sure that no group_id will ever be more than one type, then that looks OK.
You probably want to consider using an IDENTITY column to automatically assign one or more of these key values. ( Even if Celko doesn't like it! 🙂 )
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 18, 2012 at 10:19 am
ScottPletcher (12/18/2012)
If you're 100% sure that no group_id will ever be more than one type, then that looks OK.You probably want to consider using an IDENTITY column to automatically assign one or more of these key values. ( Even if Celko doesn't like it! 🙂 )
Yes, group_id will have only one type. Need to learn more about these IDENTITY 🙂
Thank you !
December 18, 2012 at 11:35 am
you can do it by using PIVOT table, but you need to sure what value of type 1 and type 2 you want to show as per group 1.
for more details in PIVOT table you can follow this link:
http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/
December 19, 2012 at 4:54 am
SELECT 'Group Name'=AA.NAME,
'Type 1'= (CASE WHEN (SELECT TOP 1 name FROM #GroupTypes BB WHERE BB.grouptype_id=AA.grouptype_id)='Type 1' THEN 'X' ELSE '-' END),
'Type 2'= (CASE WHEN (SELECT TOP 1 name FROM #GroupTypes BB WHERE BB.grouptype_id=AA.grouptype_id)='Type 2' THEN 'X' ELSE '-' END)
FROM #Groups AA
ORDER BY AA.NAME
🙂
December 20, 2012 at 2:12 am
Obrigado Carlos ) ..funcionou muito bem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply