sql server update specified column in first blank column

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;

Select allOpen in new window

Thanks, I will try it.

I don't think pivot will work for me. What I have is a table with (for example);
ApprovalData.xlsx

So what I want is records that have
ApprovalData.2.xlsx

I do not know how to get the uploaded file to show up like yours did. In the older version, embed worked.

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

Select allOpen in new window

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

Still not producing the output he wanted from #a40241031

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#

I am using this as a model with my actual table names. I will let you know if it works.
Thanks

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?

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

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?

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

Click on "Request Attention" and (briefly) describe the situation.  One of the moderators will tell you what to do.

Thanks