August 15, 2017 at 6:36 pm
Hi,I need a help in writing a query.
data looks like
ID TAB NAME Approved
--------|------------------------|-----------------------------
1 AB 1
1 CD 0
1 EF 1
2 AB 0
2 CD 1
2 EF 1
I want to write a query which returns
ID isABapproved? ISCDApproved? ISEFApproved?
-----------------------------------------------------------------------------------------------
1 YES NO YES
2 NO YES YES
August 15, 2017 at 7:02 pm
CREATE TABLE & INSERT scripts?
Feel free to explain more. I'm not sure I know how to determine what is approved.
Please read this and post again: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 16, 2017 at 1:57 am
As pietlinden suggested, you should really rad the topic on how to post T-SQL questions on this forum. It makes things a lot easier for us, as we don't need to make any assumptions about your data, and we don't need to create it. This makes people much more inclined to give you an answer.
Saying that, here is one solution, however, I have made assumptions on your datatypes:USE Sandbox;
GO
CREATE TABLE #Sample (ID int, [TAB NAME] char(2), Approved bit);
GO
INSERT INTO #Sample
VALUES (1,'AB',1),(1,'CD',0),(1,'EF',1),
(2,'AB',0),(2,'CD',1),(2,'EF',1);
SELECT *
FROM #Sample;
GO
SELECT ID,
CASE WHEN MAX(CASE WHEN [TAB NAME] = 'AB' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isABapproved?],
CASE WHEN MAX(CASE WHEN [TAB NAME] = 'CD' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isCDapproved?],
CASE WHEN MAX(CASE WHEN [TAB NAME] = 'EF' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isEFapproved?]
FROM #Sample
GROUP BY ID;
GO
DROP TABLE #Sample;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 16, 2017 at 10:58 pm
Thom A - Wednesday, August 16, 2017 1:57 AMAs pietlinden suggested, you should really rad the topic on how to post T-SQL questions on this forum. It makes things a lot easier for us, as we don't need to make any assumptions about your data, and we don't need to create it. This makes people much more inclined to give you an answer.Saying that, here is one solution, however, I have made assumptions on your datatypes:
USE Sandbox;
GOCREATE TABLE #Sample (ID int, [TAB NAME] char(2), Approved bit);
GO
INSERT INTO #Sample
VALUES (1,'AB',1),(1,'CD',0),(1,'EF',1),
(2,'AB',0),(2,'CD',1),(2,'EF',1);SELECT *
FROM #Sample;
GOSELECT ID,
CASE WHEN MAX(CASE WHEN [TAB NAME] = 'AB' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isABapproved?],
CASE WHEN MAX(CASE WHEN [TAB NAME] = 'CD' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isCDapproved?],
CASE WHEN MAX(CASE WHEN [TAB NAME] = 'EF' THEN CAST(Approved AS int) ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS [isEFapproved?]
FROM #Sample
GROUP BY ID;GO
DROP TABLE #Sample;
GO
Thanks.That worked.
August 17, 2017 at 11:53 am
Aditya-313343 - Tuesday, August 15, 2017 6:36 PMHi,I need a help in writing a query.
data looks like
ID TAB NAME Approved
--------|------------------------|-----------------------------
1 AB 1
1 CD 0
1 EF 1
2 AB 0
2 CD 1
2 EF 1I want to write a query which returns
ID isABapproved? ISCDApproved? ISEFApproved?
-----------------------------------------------------------------------------------------------
1 YES NO YES
2 NO YES YES
Select Id,
Max(case when Tabname = 'AB' and Approved = 1 then 'Yes' Else 'No' end) as ISABApprove ,
max(case when Tabname = 'CD' and Approved = 1 then 'Yes' Else 'No' end) as ISCDApprove,
MAx(case when Tabname = 'EF' and Approved = 1 then 'Yes' Else 'No' end) as ISEFApprove
from table1
group by ID
August 20, 2017 at 11:28 am
select id,
max(case when tab ='AB' and Name_approved=1 then 'Yes' else 'No' end) AS AB,
max(case when tab ='CD' and Name_approved=1 then 'Yes' else 'No' end) AS CD,
max(case when tab ='EF' and Name_approved=1 then 'Yes' else 'No' end) AS EF
from Table1
group by ID
August 20, 2017 at 3:17 pm
No you need help understanding how RDBMS works. You still basically writing assembly language programming. First of all, we need DDL. This is been netiquette on SQL for forums for over 30 years; why does this doesn't apply to you?
Unlike filesystems, which can use a physical record address, an identifier in RDBMS must be for a particular kind of entity. There is no such crap as a generic "id" in RDBMS. By definition, every table must have a key, but since you just posted a silly ASCII picture , we have to guess what the tables look like.
CREATE TABLE Foobar
(foo_id CHAR(1) NOT NULL,
tab_name CHAR(2) NOT NULL,
PRIMARY KEY (foo_id, tab_name),
stupid_flg SMALLINT NOT NULL
CHECK(stupid_flg IN (0 ,1));
You have no idea how RDBMS is supposed to work. Approval is not an attribute! It is the value of some attribute, such as "credit checking", "job application", etc. you're still writing with assembly language flags and don't know how to do a valid data model.
Also identifiers cannot be numerics because you don't do any math on them. Yes, I know in programming languages like COBOL or assembly language, there's no strong distinction between strings and numerics.
Your query is an attempt in DML to correct the DDL you messed up.
CREATE TABLE Foobar
(foo_id CHAR(1) NOT NULL PARIMARY KEY,
ab_status CHAR(1) NOT NULL
CHECK (ab_status in ('Y', 'N'),
cd_status CHAR(1) NOT NULL
CHECK (cd_status in ('Y', 'N'),
ef_status CHAR(1) NOT NULL
CHECK (ef_status in ('Y', 'N'));
You need to get a book on basic data modeling, basic RDBMS and read the forum rules about postings.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply