Solved

Query help...

Posted on 2014-04-08
17
189 Views
Last Modified: 2014-04-30
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(-)	

Open in new window


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
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +3
17 Comments
 
LVL 36

Expert Comment

by:PatHartman
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 34

Author Comment

by:Mike Eghtebas
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

Open in new window


Is it possible to make this work?

Thanks,

Mike
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Open in new window



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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 11

Accepted Solution

by:
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'

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
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 34

Author Comment

by:Mike Eghtebas
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

by:PortletPaul
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

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
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

Open in new window

Not sure how to get 6(-) for C / F2
Are you certain it's not -(6) ?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39989004
What are the data types of F1 and F2?
0
 
LVL 36

Expert Comment

by:PatHartman
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

by:PortletPaul
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 |

Open in new window

0
 
LVL 36

Expert Comment

by:PatHartman
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

by:PortletPaul
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 36

Expert Comment

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

Author Comment

by:Mike Eghtebas
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(-)

Open in new window


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 34

Author Closing Comment

by:Mike Eghtebas
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

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

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

Join & Ask a Question