Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Stored Proc help...

Posted on 2014-04-11
Medium Priority
226 Views
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(-)
``````

Thank you
0
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

LVL 34

Accepted Solution

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

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'
``````
0

LVL 70

Assisted Solution

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

LVL 15

Expert Comment

ID: 39994945
Are you working on SQL Server 2012?
0

LVL 34

Author Closing Comment

ID: 40033295
Thank you.
0

LVL 70

Expert Comment

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

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month11 days, 3 hours left to enroll