Solved

# Query help...

Posted on 2014-04-08
187 Views
``````Table1 exists:
Name	F1	F2	flag
A	7	3	c
B	13	4	c
C	0	6	c
A	11	8	e
B	19	9	e
C	4	0	e

Query1 to produce the following from Table1
Name	  F1	        F2
A	7(11)	       3(8)
B	13(19)	       4(9)
C	-(4)	       6(-)
``````

Question: Could you please make SQL to produce Query1 from Table1?

The values in this query for columns F1 and F2 are displayed as c(e)  per "flag" field where null or zero values are substituted by "-".

Thank you.
0
Question by:Mike Eghtebas
• 5
• 4
• 4
• +3

LVL 35

Expert Comment

ID: 39987016
Assuming the flag has some meaning, create two queries.  One to select flag value c and the other to select flag value e.  Then create a third query that joins the first two.

Select q1.[Name], IIf(Nz(q1.F1,0) = 0 , "-", q1.F1) & "(" & IIf(Nz(q2.F1,0) = 0, "-", q2.F1) & ")" As fld1, IIf(Nz(q1.F2,0) = 0, "-", q1.F2) & "(" & IIf(Nz(q2.F2,0) = 0, "-", q2.F2) & "0" as fld2
From q1 inner join q2 on q1.[Name] = q2.[Name]
0

LVL 33

Author Comment

ID: 39987072
I made the following using single query for one field only, no errors but no result yet (which is an error by itself):

``````Select
c.[cmName]
, IIf(Nz(c.onService,0) = 0 , "-", c.onService) & "(" & IIf(Nz(e.onService,0) = 0, "-", e.onService) & ")" As onServiceStr
From (
Select * From tStat Where CforCaseEforEvent="c") c
Inner Join  (Select * From tStat Where CforCaseEforEvent="e") e
on c.cmName=e.cmName
``````

Is it possible to make this work?

Thanks,

Mike
0

LVL 120

Expert Comment

ID: 39987267
place this codes in a regular module

``````Function getValues(vName As String, idx As Integer)
Dim rs As DAO.Recordset, sql As String, xVal As String

If idx = 1 Then
sql = "select f1 from table3 where [name]='" & vName & "' order by flag"
Else
sql = "select f2 from table3 where [name]='" & vName & "' order by flag"

End If

Set rs = CurrentDb.OpenRecordset(sql)
Do Until rs.EOF
If xVal = "" Then
xVal = IIf(rs(0) = 0 Or IsNull(rs(0)), "-", rs(0))
Else
xVal = xVal & "(" & IIf(rs(0) = 0 Or IsNull(rs(0)), "-", rs(0)) & ")"

End If
rs.MoveNext
Loop
getValues = xVal
End Function
``````

then create a query like this

SELECT Table1.Name, getvalues([name],1) AS F1, getvalues([name],2) AS F2
FROM Table1
GROUP BY Table1.Name, getvalues([name],1), getvalues([name],2);

.
0

LVL 11

Accepted Solution

John_Vidmar earned 500 total points
ID: 39987277
``````SELECT	c.name
,	F1	= isnull(nullif(cast(c.F1 as varchar),0),'-')
+ '('
+ isnull(nullif(cast(e.F1 as varchar),0),'-')
+ ')'
,	F2	= isnull(nullif(cast(c.F2 as varchar),0),'-')
+ '('
+ isnull(nullif(cast(e.F2 as varchar),0),'-')
+ ')'
FROM	table1	c
JOIN	table1	e 	ON	c.name = e.name
AND	c.flag = 'c'
AND	e.flag = 'e'
``````
0

LVL 48

Expert Comment

ID: 39987476
not entirely certain of access syntax, but it appears to be a group by that's needed with min() and max() values concatenated

select
name
, min( IIf(Nz(F1,0) = 0 , "-", F1) ) & '(' + max( IIf(Nz(F1,0) = 0 , "-", F1) ) & ')' as F1
, min( IIf(Nz(F2,0) = 0 , "-", F2) ) & '(' + max( IIf(Nz(F2,0) = 0 , "-", F2) ) & ')' as F2
from yourtable
group by name
0

LVL 33

Author Comment

ID: 39987516
now checking the solution.

Hi Paul,

FYI, I will be using this in SQL Server. I am in process of converting an Access prototype application to asp version with SQL Server as backend.

Thanks,

Mike
0

LVL 48

Expert Comment

ID: 39987537
mmmm, not quite what you asked for
``````    INSERT INTO yourtable
([Name], [F1], [F2], [flag])
VALUES
('A', 7, 3, 'c'),
('B', 13, 4, 'c'),
('C', 0, 6, 'c'),
('A', 11, 8, 'e'),
('B', 19, 9, 'e'),
('C', 4, 0, 'e')
;

**Query 1**:

SELECT
name
,   MIN( CASE WHEN ISNULL(F1,0) = 0 THEN '-' ELSE convert(varchar(5),F1) END )
+ '('
+ MAX( CASE WHEN ISNULL(F1,0) = 0 THEN '-' ELSE convert(varchar(5),F1) END )
+ ')'
,   MIN( CASE WHEN ISNULL(F2,0) = 0 THEN '-' ELSE convert(varchar(5),F2) END )
+ '('
+ MAX( CASE WHEN ISNULL(F2,0) = 0 THEN '-' ELSE convert(varchar(5),F2) END )
+ ')'
FROM yourtable
GROUP BY name

**[Results][2]**:

| NAME | COLUMN_1 | COLUMN_2 |
|------|----------|----------|
|    A |    11(7) |     3(8) |
|    B |   13(19) |     4(9) |
|    C |     -(4) |     -(6) |

[1]: http://sqlfiddle.com/#!3/a7ce1/4
``````
0

LVL 48

Expert Comment

ID: 39987570
closer...
``````    INSERT INTO yourtable
([Name], [F1], [F2], [flag])
VALUES
('A', 7, 3, 'c'),
('B', 13, 4, 'c'),
('C', 0, 6, 'c'),
('A', 11, 8, 'e'),
('B', 19, 9, 'e'),
('C', 4, 0, 'e')
;

**Query 1**:

SELECT
name
,   CASE WHEN min(ISNULL(F1,0)) = 0 THEN '-' ELSE convert(varchar(5),min(f1)) END
+ '('
+ CASE WHEN max(ISNULL(F1,0)) = 0 THEN '-' ELSE convert(varchar(5),max(f1)) END
+ ')'
,   CASE WHEN min(ISNULL(F2,0)) = 0 THEN '-' ELSE convert(varchar(5),min(f2)) END
+ '('
+ CASE WHEN max(ISNULL(F2,0)) = 0 THEN '-' ELSE convert(varchar(5),max(f2)) END
+ ')'
FROM yourtable
GROUP BY name

**[Results][2]**:

| NAME | COLUMN_1 | COLUMN_2 |
|------|----------|----------|
|    A |    7(11) |     3(8) |
|    B |   13(19) |     4(9) |
|    C |     -(4) |     -(6) |

[1]: http://sqlfiddle.com/#!3/86767/1
``````
Not sure how to get 6(-) for C / F2
Are you certain it's not -(6) ?
0

LVL 32

Expert Comment

ID: 39989004
What are the data types of F1 and F2?
0

LVL 35

Expert Comment

ID: 39989046
Is it possible to make this work?
Get rid of the subselect and use a straight join of the two queries.  That will allow you to test the individual pieces to see where the error is.

1.  Is your question - what is the syntax for formatting the fields as --- -(4)?  I showed you how to do it with IIf()'s inside the query.  Rey offered a function -  I don't created functions unless there is no other way since I use primarily SQL Server BE's and VBA/UDF functions cannot be sent to the server.  That means Access has to take apart the query and decide what can be sent and what can't and it doesn't always make the correct decision which causes Access to request way too much data.
2. Are you trying to do this with Access SQL syntax?  I ask because several folks have posted T-SQL solutions because you posted the question to the SQL Server forum.
0

LVL 48

Expert Comment

ID: 39990512
@Pat see ID: 39987516
>>"FYI, I will be using this in SQL Server. I am in process of converting an Access prototype application to asp version with SQL Server as backend."

additionally a self join isn't going to produce 3 rows
``````    CREATE TABLE yourtable
([Name] varchar(1), [F1] int, [F2] int, [flag] varchar(1))
;

INSERT INTO yourtable
([Name], [F1], [F2], [flag])
VALUES
('A', 7, 3, 'c'),
('B', 13, 4, 'c'),
('C', 0, 6, 'c'),
('A', 11, 8, 'e'),
('B', 19, 9, 'e'),
('C', 4, 0, 'e')
;

**Query 1**:

select
*
From yourtable q1
inner join yourtable q2 on q1.[Name] = q2.[Name]

**[Results][2]**:

| NAME | F1 | F2 | FLAG |
|------|----|----|------|
|    A |  7 |  3 |    c |
|    A | 11 |  8 |    e |
|    B | 13 |  4 |    c |
|    B | 19 |  9 |    e |
|    C |  0 |  6 |    c |
|    C |  4 |  0 |    e |
|    A |  7 |  3 |    c |
|    A | 11 |  8 |    e |
|    B | 13 |  4 |    c |
|    B | 19 |  9 |    e |
|    C |  0 |  6 |    c |
|    C |  4 |  0 |    e |
``````
0

LVL 35

Expert Comment

ID: 39992306
The self join produces a Cartesian Product.  You need to get set 1 and set 2 from the table and join set 1 to set 2.  Set 1 contains the records with the c flag.  set 2 contains the records with the e flag.  If you do this with SQL Server, you could use views instead of queries.  View 1 would select the c records, view 2 would select the e records.

If you have only the two flag values then the following self join might work:

select
*
From yourtable q1
inner join yourtable q2 on q1.[Name] = q2.[Name] and q1.[Flag] <> q2.[Flag]

Also,
If you only want T-SQL syntax, you should specify that and not even post in the Access forum.  The functions I used to format the calculated column will not work in T-SQL either.

Just because the BE is SQL Server does not mean that you can't use querydefs assuming that the FE will be Access.  If the FE is not Access and the BE is not Access, then the question is in the wrong category.
0

LVL 48

Expert Comment

ID: 39992937
Good point regarding the topics Pat.

Regarding the self-join, my intended point was that a self-join isn't wanted for the solution as there are only 3 rows in the expected result (from the original 6). To reduce the rows a GROUP BY is required.
0

LVL 35

Expert Comment

ID: 39994193
A group by will not reduce the Cartesian Product to what the OP requested but my additional criteria should.
0

LVL 33

Author Comment

ID: 39994578
Rey Obrero and PatHartman,
The final code will be transformed to be used in SQL Server. I have nor mentioned this in my original question (the first phase of my question). In the follow up question I am preparing to post shortly, I will request stored proc solution.

John_Vidmar,
Your solution works perfectly.

PortletPaul,
re:> Not sure how to get 6(-) for C / F2.... Are you certain it's not -(6) ?

Answer: The patern is c-value(e-value) which means we need to have 6(-) not -(6).

Your solution (ID: 39987537) works but it takes longer to display the results.

re:> If you only want T-SQL syntax, you should specify that and not even post in the Access forum.

Answer: You are right. This is what I will do next. This will be the second phase of my initial question.

awking00,
re:> What are the data types of F1 and F2?

Answer: They are integer.

================
I will close this question shortly.

These discussions have helped me with the direction the application needs to take.

``````tSource:
Name	F1	F2	flag
A	7	3	c
B	13	4	c
C	0	6	c
A	11	8	e
B	19	9	e
C	4	0	e

tTarget
Name	  F1	        F2
A	7(11)	       3(8)
B	13(19)	       4(9)
C	-(4)	       6(-)
``````

Now, in the new question I will post in SQL Server section shortly, I will ask for help to put together a stored procedure (possibly using cursor) to take data from tSource and put it in tTarget. This way, I can schedule to run the stored procedure once every hour (or on demand) instead of with each changes users make to the data.

Thank you all for very helpful inputs.

Mike

Here is the link to the new question:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28410672.html
0

LVL 33

Author Closing Comment

ID: 40033286
Sorry for the delay and thank you for good comments.

As the logo of EE (a race car flag indicates), the first (fastest) solution gets the points.

Regards,

Mike
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

#### 777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.