• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

sql server update specified column in first blank column

I have data with a level field with values 1 to 6. I want to list this data across. So I would like to build a temp table with Level1, level2, etc. So I would like to update from table1 that has the field Level containing a 1-6 and place the data related to that level into a corresponding column in the temp table. what would be the best technique to do this?
0
qbjgqbjg
Asked:
qbjgqbjg
  • 11
  • 4
  • 2
1 Solution
 
nemws1Commented:
Sounds like you want to do a PIVOT.

This should get you started:
CREATE TABLE levels (
	LEVEL INT
	, DATA VARCHAR(20)
	);


INSERT INTO levels VALUES (1, 'lowest');
INSERT INTO levels VALUES (2, 'low');
INSERT INTO levels VALUES (3, 'a little bit low');
INSERT INTO levels VALUES (4, 'a little bit high');
INSERT INTO levels VALUES (5, 'high');
INSERT INTO levels VALUES (6, 'highest');

SELECT *
FROM levels
PIVOT(MAX(DATA) FOR LEVEL IN (
			[1], [2], [3], [4], [5], [6]
			)) AS maxdata;

Open in new window

0
 
qbjgqbjgConsultantAuthor Commented:
Thanks, I will try it.
0
 
qbjgqbjgConsultantAuthor Commented:
I don't think pivot will work for me. What I have is a table with (for example);
ApprovalData.xlsx
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
qbjgqbjgConsultantAuthor Commented:
So what I want is records that have
ApprovalData.2.xlsx
0
 
qbjgqbjgConsultantAuthor Commented:
I do not know how to get the uploaded file to show up like yours did. In the older version, embed worked.
0
 
nemws1Commented:
Hmm... the issues is that the data from one column in your desired result set doesn't correlate at all to the data in another column.

This is the closest I can get, but it is obviously not what you want.

SELECT DISTINCT AD.QUEUE_TYPE
	, AD.QUEUE_NAME
	, AD.QUEUE_DESC
	, AD.QUEUE_LEVEL
	, Q1.QUEUE_LEVEL
	, Q1.QUEUE_NAME
	, Q1.APPROVER
	, Q1.CAN_APPROVE
	, Q1.CAN_PASS
	, Q1.NOTIFY
	, Q2.QUEUE_LEVEL
	, Q2.APPROVER
	, Q2.CAN_APPROVE
	, Q2.CAN_PASS
	, Q2.NOTIFY
	, Q3.QUEUE_LEVEL
	, Q3.APPROVER
	, Q3.CAN_APPROVE
	, Q3.CAN_PASS
	, Q3.NOTIFY
	, Q4.QUEUE_LEVEL
	, Q4.APPROVER
	, Q4.CAN_APPROVE
	, Q4.CAN_PASS
	, Q4.NOTIFY
	, Q5.QUEUE_LEVEL
	, Q5.APPROVER
	, Q5.CAN_APPROVE
	, Q5.CAN_PASS
	, Q5.NOTIFY
	, Q6.QUEUE_LEVEL
	, Q6.APPROVER
	, Q6.CAN_APPROVE
	, Q6.CAN_PASS
	, Q6.NOTIFY
FROM ApprovalData AS AD
	LEFT JOIN ApprovalData AS Q1
		ON Q1.QUEUE_TYPE = AD.QUEUE_TYPE
			AND Q1.QUEUE_NAME = AD.QUEUE_NAME
			AND Q1.QUEUE_LEVEL = 1
			AND Q1.APPROVER = AD.APPROVER
	LEFT JOIN ApprovalData AS Q2
		ON Q2.QUEUE_TYPE = AD.QUEUE_TYPE
			AND Q2.QUEUE_NAME = AD.QUEUE_NAME
			AND Q2.QUEUE_LEVEL = 2
			AND Q2.APPROVER = AD.APPROVER
	LEFT JOIN ApprovalData AS Q3
		ON Q3.QUEUE_TYPE = AD.QUEUE_TYPE
			AND Q3.QUEUE_NAME = AD.QUEUE_NAME
			AND Q3.QUEUE_LEVEL = 3
			AND Q3.APPROVER = AD.APPROVER
	LEFT JOIN ApprovalData AS Q4
		ON Q4.QUEUE_TYPE = AD.QUEUE_TYPE
			AND Q4.QUEUE_NAME = AD.QUEUE_NAME
			AND Q4.QUEUE_LEVEL = 4
			AND Q4.APPROVER = AD.APPROVER
	LEFT JOIN ApprovalData AS Q5
		ON Q5.QUEUE_TYPE = AD.QUEUE_TYPE
			AND Q5.QUEUE_NAME = AD.QUEUE_NAME
			AND Q5.QUEUE_LEVEL = 5
			AND Q5.APPROVER = AD.APPROVER
	LEFT JOIN ApprovalData AS Q6
		ON Q6.QUEUE_TYPE = AD.QUEUE_TYPE
			AND Q6.QUEUE_NAME = AD.QUEUE_NAME
			AND Q6.QUEUE_LEVEL = 6
			AND Q6.APPROVER = AD.APPROVER

ORDER BY AD.QUEUE_TYPE
	, AD.QUEUE_NAME
	, AD.QUEUE_DESC
	, AD.QUEUE_LEVEL
	, Q1.APPROVER
	, Q2.APPROVER
	, Q3.APPROVER
	, Q4.APPROVER
	, Q5.APPROVER
	, Q6.APPROVER

Open in new window

0
 
Scott PletcherSenior DBACommented:
SELECT
    QUEUE_TYPE, QUEUE_NAME, QUEUE_DESC,
    1 AS QUEUE_LEVEL,
    MAX(CASE WHEN QUEUE_LEVEL = 1 THEN APPROVER END) AS APPROVER,
    MAX(CASE WHEN QUEUE_LEVEL = 1 THEN CAN_APPROVE END) AS CAN_APPROVE,
    MAX(CASE WHEN QUEUE_LEVEL = 1 THEN CAN_PASS END) AS CAN_PASS,
    MAX(CASE WHEN QUEUE_LEVEL = 1 THEN NOTIFY END) AS NOTIFY,
    2 AS QUEUE_LEVEL,
    MAX(CASE WHEN QUEUE_LEVEL = 2 THEN APPROVER END) AS APPROVER,
    MAX(CASE WHEN QUEUE_LEVEL = 2 THEN CAN_APPROVE END) AS CAN_APPROVE,
    MAX(CASE WHEN QUEUE_LEVEL = 2 THEN CAN_PASS END) AS CAN_PASS,
    MAX(CASE WHEN QUEUE_LEVEL = 2 THEN NOTIFY END) AS NOTIFY,
    3 AS QUEUE_LEVEL,
    MAX(CASE WHEN QUEUE_LEVEL = 3 THEN APPROVER END) AS APPROVER,
    MAX(CASE WHEN QUEUE_LEVEL = 3 THEN CAN_APPROVE END) AS CAN_APPROVE,
    MAX(CASE WHEN QUEUE_LEVEL = 3 THEN CAN_PASS END) AS CAN_PASS,
    MAX(CASE WHEN QUEUE_LEVEL = 3 THEN NOTIFY END) AS NOTIFY,
    4 AS QUEUE_LEVEL,
    MAX(CASE WHEN QUEUE_LEVEL = 4 THEN APPROVER END) AS APPROVER,
    MAX(CASE WHEN QUEUE_LEVEL = 4 THEN CAN_APPROVE END) AS CAN_APPROVE,
    MAX(CASE WHEN QUEUE_LEVEL = 4 THEN CAN_PASS END) AS CAN_PASS,
    MAX(CASE WHEN QUEUE_LEVEL = 4 THEN NOTIFY END) AS NOTIFY,
    5 AS QUEUE_LEVEL,
    MAX(CASE WHEN QUEUE_LEVEL = 5 THEN APPROVER END) AS APPROVER,
    MAX(CASE WHEN QUEUE_LEVEL = 5 THEN CAN_APPROVE END) AS CAN_APPROVE,
    MAX(CASE WHEN QUEUE_LEVEL = 5 THEN CAN_PASS END) AS CAN_PASS,
    MAX(CASE WHEN QUEUE_LEVEL = 5 THEN NOTIFY END) AS NOTIFY,
    6 AS QUEUE_LEVEL,
    MAX(CASE WHEN QUEUE_LEVEL = 6 THEN APPROVER END) AS APPROVER,
    MAX(CASE WHEN QUEUE_LEVEL = 6 THEN CAN_APPROVE END) AS CAN_APPROVE,
    MAX(CASE WHEN QUEUE_LEVEL = 6 THEN CAN_PASS END) AS CAN_PASS,
    MAX(CASE WHEN QUEUE_LEVEL = 6 THEN NOTIFY END) AS NOTIFY
FROM ApprovalData
GROUP BY
    QUEUE_TYPE, QUEUE_NAME, QUEUE_DEC
ORDER BY
    QUEUE_TYPE, QUEUE_NAME, QUEUE_DESC
0
 
nemws1Commented:
Still not producing the output he wanted from #a40241031
0
 
Scott PletcherSenior DBACommented:
SELECT
     QUEUE_TYPE, QUEUE_NAME, QUEUE_DESC,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 1 THEN CAST(QUEUE_LEVEL AS varchar(10)) END), '') AS QUEUE_LEVEL,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 1 THEN APPROVER END), '') AS APPROVER,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 1 THEN CAN_APPROVE END), '') AS CAN_APPROVE,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 1 THEN CAN_PASS END), '') AS CAN_PASS,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 1 THEN NOTIFY END), '') AS NOTIFY,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 2 THEN CAST(QUEUE_LEVEL AS varchar(10)) END), '') AS QUEUE_LEVEL,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 2 THEN APPROVER END), '') AS APPROVER,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 2 THEN CAN_APPROVE END), '') AS CAN_APPROVE,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 2 THEN CAN_PASS END), '') AS CAN_PASS,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 2 THEN NOTIFY END), '') AS NOTIFY,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 3 THEN CAST(QUEUE_LEVEL AS varchar(10)) END), '') AS QUEUE_LEVEL,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 3 THEN APPROVER END), '') AS APPROVER,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 3 THEN CAN_APPROVE END), '') AS CAN_APPROVE,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 3 THEN CAN_PASS END), '') AS CAN_PASS,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 3 THEN NOTIFY END), '') AS NOTIFY,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 4 THEN CAST(QUEUE_LEVEL AS varchar(10)) END), '') AS QUEUE_LEVEL,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 4 THEN APPROVER END), '') AS APPROVER,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 4 THEN CAN_APPROVE END), '') AS CAN_APPROVE,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 4 THEN CAN_PASS END), '') AS CAN_PASS,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 4 THEN NOTIFY END), '') AS NOTIFY,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 5 THEN CAST(QUEUE_LEVEL AS varchar(10)) END), '') AS QUEUE_LEVEL,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 5 THEN APPROVER END), '') AS APPROVER,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 5 THEN CAN_APPROVE END), '') AS CAN_APPROVE,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 5 THEN CAN_PASS END), '') AS CAN_PASS,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 5 THEN NOTIFY END), '') AS NOTIFY,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 6 THEN CAST(QUEUE_LEVEL AS varchar(10)) END), '') AS QUEUE_LEVEL,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 6 THEN APPROVER END), '') AS APPROVER,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 6 THEN CAN_APPROVE END), '') AS CAN_APPROVE,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 6 THEN CAN_PASS END), '') AS CAN_PASS,
     ISNULL(MAX(CASE WHEN QUEUE_LEVEL = 6 THEN NOTIFY END), '') AS NOTIFY
 FROM (
     SELECT *,
         ROW_NUMBER() OVER(PARTITION BY QUEUE_TYPE, QUEUE_NAME, QUEUE_DESC, QUEUE_LEVEL ORDER BY QUEUE_LEVEL) AS set#
     FROM ApprovalData
) AS ad
 GROUP BY
     QUEUE_TYPE, QUEUE_NAME, QUEUE_DESC, set#
 ORDER BY
     QUEUE_TYPE, QUEUE_NAME, QUEUE_DESC, set#
0
 
qbjgqbjgConsultantAuthor Commented:
I am using this as a model with my actual table names. I will let you know if it works.
Thanks
0
 
qbjgqbjgConsultantAuthor Commented:
I don't think I understand this well enough to use it. How would this go from a single column of data to multiple columns in the same record?
0
 
qbjgqbjgConsultantAuthor Commented:
I am trying to take another approach using temp tables. I am having a problem with using rownumber.  I tried a few different things, but have not been able to get it right.
This is my code:
SET IDENTITY_INSERT #DataLevel ON

CREATE TABLE #DataLevel  
  (
   QUEUE_TYPE VARCHAR(2)
  , QUEUE_NAME char(10) NULL
  , QUEUE_DESC char(40) NULL
  , QUEUE_LEVEL smallint NULL
  , APPROVER char(10) NULL
  , CAN_APPROVE char(1) NULL
  , CAN_PASS char(1) NULL
  , NOTIFY char(1) NULL
  , RowNumber int IDENTITY (1, 1)
 )
 INSERT INTO #DataLevel
 (QUEUE_TYPE
       , QUEUE_NAME
       , QUEUE_DESC
       , QUEUE_LEVEL
       , APPROVER
       , CAN_APPROVE
       , CAN_PASS
       , NOTIFY
       , RowNumber
  )
      SELECT
      ESPQAPRD.queue_type as QUEUE_TYPE
       , ESPQREFR.QUEUE_NAME as QUEUE_NAME
       , ESPQREFR.QUEUE_DESC as QUEUE_DESC
       , ESPQLVLD.QUEUE_LEVEL as LEVEL
       , ESPQAPRD.APPROVER as APPROVER
       , ESPQAPRD.CAN_APPROVE as CAN_APPROVE
       , ESPQAPRD.CAN_PASS as CAN_PASS
       , ESPQAPRD.NOTIFY as NOTIFY
       , RowNumber
       
   FROM  
 (((ESPQAPRD  INNER JOIN ESPQREFR
 ON ESPQAPRD.QUEUE_NAME=ESPQREFR.QUEUE_NAME)
 INNER JOIN ESPQLVLD
 ON ((ESPQAPRD.QUEUE_LEVEL=ESPQLVLD.QUEUE_LEVEL)
 AND (ESPQAPRD.QUEUE_NAME=ESPQLVLD.QUEUE_NAME))
 AND (ESPQAPRD.queue_type=ESPQLVLD.queue_type))
 INNER JOIN ESPQUSRD
 ON (ESPQAPRD.QUEUE_NAME=ESPQUSRD.QUEUE_NAME)
 AND (ESPQAPRD.queue_type=ESPQUSRD.queue_type))
 INNER JOIN ESXUSERH
 ON ESPQUSRD.USER_ID=ESXUSERH.USERID
 
 WHERE  
 ESXUSERH.USER_STATUS='A'
 AND  NOT (ESPQREFR.QUEUE_NAME>='1'
 AND ESPQREFR.QUEUE_NAME<='99999')
 AND ESPQREFR.QUEUE_DESC NOT LIKE '%DO NOT USE%'
 AND ESPQLVLD.QUEUE_LEVEL = 1
SET IDENTITY_INSERT #DataLevel OFF
0
 
qbjgqbjgConsultantAuthor Commented:
I think I have a way to do this. Similar to the code above. When I complete it tomorrow, I will post it. What do you do about a question you figure out yourself?
0
 
qbjgqbjgConsultantAuthor Commented:
This is still not working correctly. I am trying to load all of the data for a queue into records that will have all the levels in one record. There can be multiple lines per queue.ApprovalTEST.txt
0
 
qbjgqbjgConsultantAuthor Commented:
I have sql code that produces the result I am looking for. So, how do I handle this question. I appreciate the suggestions given, but I was previously reprimanded for accepting a solution that was not perfect even though I had used it as a model for my code and it worked. Here is my code. It is lengthy and maybe it could be done some other way that would be more efficient. If there are any suggestions, I would appreciate them. The jist of what I am doing is building a temp table assigning Rownumber by my group. Then building a 2nd temp table where I use Rownumber to pull the data by group into single records. I end up with records that have null values in quite a few records, so I delete those.
ApprovalQueueCommand.xxx.sql
0
 
nemws1Commented:
Click on "Request Attention" and (briefly) describe the situation.  One of the moderators will tell you what to do.
0
 
qbjgqbjgConsultantAuthor Commented:
Thanks
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 11
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now