• Status: Solved
• Priority: Medium
• Security: Public
• Views: 235

# Stored Proc help...

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
Mike Eghtebas
3 Solutions

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

Commented:
``````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

Senior DBACommented:
--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

Research & Development ManagerCommented:
Are you working on SQL Server 2012?
0

Database and Application DeveloperAuthor Commented:
Thank you.
0

Senior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.