?
Solved

Stored Proc help...

Posted on 2014-04-11
6
Medium Priority
?
215 Views
Last Modified: 2014-05-01
In table Source below, there is a flag field with string values, c or e. Could you please help me with a stored proc to read table Source and put it table Target?

The c-value(e-value) from columns F1 and F2 will show up in the Source table while Name A, B, C each will show only once.
Source:
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
			
Target
Name	  F1	        F2	
A	7(11)	       3(8)	
B	13(19)	       4(9)	
C	-(4)	       6(-)

Open in new window


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
6 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 800 total points
ID: 39994636
You didn't provide any data type information so you may need to cast some values to make the query below work.

INSERT INTO Target (Name, F1, F2)
SELECT c.Name,
   CASE c.F1 WHEN '0' THEN '-' ELSE c.F1 END + '(' + CASE e.F1 WHEN '0' THEN '-' ELSE e.F1 END + ')',
   CASE c.F2 WHEN '0' THEN '-' ELSE c.F2 END + '(' + CASE e.F2 WHEN '0' THEN '-' ELSE e.F2 END + ')',
FROM Source AS c
INNER JOIN Source AS e
   ON c.Name = e.Name
   AND C.flag = 'c'
   AND e.flag = 'e'
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 600 total points
ID: 39994780
INSERT	Target
(	Name
,	F1
,	F2
)
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	Source	c 
JOIN	Source	e 	ON	c.name = e.name
			AND	c.flag = 'c'
			AND	e.flag = 'e'

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 600 total points
ID: 39994877
--INSERT INTO dbo.target ( Name, F1, F2 )
SELECT
    Name,
    ISNULL(NULLIF(CAST(MAX(CASE WHEN flag = 'c' THEN F1 END) AS varchar(10)), 0), '-') + '(' +
    ISNULL(NULLIF(CAST(MAX(CASE WHEN flag = 'e' THEN F1 END) AS varchar(10)), 0), '-') + ')' AS F1,
    ISNULL(NULLIF(CAST(MAX(CASE WHEN flag = 'c' THEN F2 END) AS varchar(10)), 0), '-') + '(' +
    ISNULL(NULLIF(CAST(MAX(CASE WHEN flag = 'e' THEN F2 END) AS varchar(10)), 0), '-') + ')' AS F2
FROM source
GROUP BY Name
ORDER BY Name


Or:
SELECT ...
INTO dbo.target
...
If you want to create a brand new target table from scratch.
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39994945
Are you working on SQL Server 2012?
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40033295
Thank you.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40034541
Huh?  The accepted solution does not and cannot produce the output as stated in the initial request, because it will never combine/merge values from different rows.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

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.

Join & Ask a Question