2

Table1 contains ServerName and a list of applications (from Application01 to Application20) installed on the server.

Table 1

Now I need to import data from Table1 to Table2. The final Table2 should look as following:

Table2

How can I do this with a Stored Procedure?

0

2 Answers 2

1

If you're feeling adventurous, give UNPIVOT a try:

--Setup demo data
DECLARE @T TABLE (
    ServerName VARCHAR(20)
    ,Application01 VARCHAR(20)
    ,Application02 VARCHAR(20)
    ,Application03 VARCHAR(20)
    ,Application04 VARCHAR(20)
    ,Application05 VARCHAR(20)
    ,Application06 VARCHAR(20)
    ,Application07 VARCHAR(20)
    ,Application08 VARCHAR(20)
    ,Application09 VARCHAR(20)
    ,Application10 VARCHAR(20)
    ,Application11 VARCHAR(20)
    ,Application12 VARCHAR(20)
    ,Application13 VARCHAR(20)
    ,Application14 VARCHAR(20)
    ,Application15 VARCHAR(20)
    ,Application16 VARCHAR(20)
    ,Application17 VARCHAR(20)
    ,Application18 VARCHAR(20)
    ,Application19 VARCHAR(20)
    ,Application20 VARCHAR(20)
    )

INSERT INTO @T (ServerName,Application01,Application02)
VALUES ('Server1','ApplicationXY','ApplicationXX')
    ,('Server2','ApplicationZZ','ApplicationYY')
    ,('Server3','ApplicationXZ','ApplicationZX');

;

--The actual code
WITH _cte
AS (
    SELECT ServerName
        ,ApplicationValue
    FROM (
        SELECT *
        FROM @T
        ) AS t
    UNPIVOT(Applicationvalue FOR Application IN (
                Application01
                ,Application02
                ,Application03
                ,Application04
                ,Application05
                ,Application06
                ,Application07
                ,Application08
                ,Application09
                ,Application10
                ,Application11
                ,Application12
                ,Application13
                ,Application14
                ,Application15
                ,Application16
                ,Application17
                ,Application18
                ,Application19
                ,Application20
                )) AS ApplicationValue
    )
--INSERT INTO <TargetTable>(ServerName, Application)
SELECT ServerName, Applicationvalue
FROM _cte
WHERE Applicationvalue IS NOT NULL

| ServerName | Applicationvalue |
|------------|------------------|
| Server1    | ApplicationXY    |
| Server1    | ApplicationXX    |
| Server2    | ApplicationZZ    |
| Server2    | ApplicationYY    |
| Server3    | ApplicationXZ    |
| Server3    | ApplicationZX    |
0
2

IMHO you should use UNION (yes, 19 times)

create table tbl (server_name varchar(100), app1 varchar(100), app2 varchar(100), app3 varchar(100));
insert into tbl values
('server1', 'app xx', 'app xy', 'app yx'),
('server2', 'app xx', 'app xy', null),
('server3', 'app xx', 'app xy', null);
GO
3 rows affected
select server_name, app1 as application from tbl where app1 is not null
union
select server_name, app2 as application from tbl where app2 is not null
union
select server_name, app3 as application from tbl where app3 is not null
GO
server_name | application
:---------- | :----------
server1     | app xx     
server1     | app xy     
server1     | app yx     
server2     | app xx     
server2     | app xy     
server3     | app xx     
server3     | app xy     

dbfiddle here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.