Navigation database columns to zha get ah, feel very difficult-CodePudding

How to generate such a format

CodePudding user response:

In the raw data, there was no record is equal to the "wal-mart" enterprise name?

CodePudding user response:

 
The CREATE TABLE # T 
(ITEM_NAME VARCHAR (20), 
QTY INT, 
ITEM_NO VARCHAR (10), 
BUYER VARCHAR (10), 
PRICE INT, 
DELIVERY_DAY INT) 

INSERT INTO # T 
SELECT 'MEMORY', 11, '001', 'AAAAAA, 999, 9 UNION ALL 
12, SELECT the 'POWER', '002', 'AAAAAA, 888, 8 UNION ALL 
SELECT 'MEMORY', 11, '001', 'BBBBB, 777, 7 UNION ALL 
12, SELECT the 'POWER', '002', 'BBBBB, 666, 6 

WITH CTE_1 
AS 
(SELECT *, DENSE_RANK () OVER (ORDER BY ITEM_NAME) AS SEQ_1 FROM # T), 

CTE_2 
AS 
(SELECT ITEM_NAME, 'AS the VALUE, the BUYER, SEQ_1, 1 AS SEQ_2 FROM CTE_1 
UNION ALL 
SELECT the 'PRICE', CAST (PRICE AS VARCHAR (10)), the BUYER, AS SEQ_2 SEQ_1, 2 FROM CTE_1 
UNION ALL 
SELECT 'DELIVERY_DAY, CAST (DELIVERY_DAY AS VARCHAR (10)), BUYER, SEQ_1, 3 AS SEQ_2 FROM CTE_1) 

SELECT ITEM_NAME AS BUYER, [AAAAAA], [BBBBB] FROM CTE_2 
The PIVOT (MAX (VALUE) FOR BUYER IN ([AAAAAA], [BBBBB])) 
 BThe ORDER BY SEQ_1, SEQ_2

If an enterprise name is not fixed, it is dynamic,

CodePudding user response:

The enterprise name are not fixed

CodePudding user response:

reference fanqieo reply: 3/f

for enterprise name are not fixed

 
The CREATE TABLE # T 
(ITEM_NAME VARCHAR (20), 
QTY INT, 
ITEM_NO VARCHAR (10), 
BUYER VARCHAR (10), 
PRICE INT, 
DELIVERY_DAY INT) 

INSERT INTO # T 
SELECT 'MEMORY', 11, '001', 'AAAAAA, 999, 9 UNION ALL 
12, SELECT the 'POWER', '002', 'AAAAAA, 888, 8 UNION ALL 
SELECT 'MEMORY', 11, '001', 'BBBBB, 777, 7 UNION ALL 
12, SELECT the 'POWER', '002', 'BBBBB, 666, 6 


DECLARE @ SQL VARCHAR (1000) 

SELECT @ SQL=ISNULL (@ SQL + ', ', ') + '[' + BUYER +'] '
The FROM (SELECT DISTINCT BUYER FROM # T) AS A 

The SET @ SQL='WITH CTE_1 
AS 
(SELECT *, DENSE_RANK () OVER (ORDER BY ITEM_NAME) AS SEQ_1 FROM # T), 

CTE_2 
AS 
(SELECT ITEM_NAME, ' ' ' 'AS the VALUE of BUYER, SEQ_1, 1 AS SEQ_2 FROM CTE_1 
UNION ALL 
SELECT "PRICE", CAST (PRICE AS VARCHAR (10)), the BUYER, AS SEQ_2 SEQ_1, 2 FROM CTE_1 
UNION ALL 
SELECT 'DELIVERY_DAY', CAST (DELIVERY_DAY AS VARCHAR (10)), BUYER, SEQ_1, 3 AS SEQ_2 FROM CTE_1) 

SELECT ITEM_NAME AS BUYER, 'SQL + + @ the FROM CTE_2 
The PIVOT (MAX (VALUE) FOR BUYER (+ SQL + '@')) IN B 
The ORDER BY SEQ_1, SEQ_2 '

The EXEC (@ SQL)

CodePudding user response:

The bosses, you too cattle technology, have what tutorial can recommend? The SQL can play such a sneak

CodePudding user response:

reference 5 floor fanqieo reply:

, you're too cattle technology, have what tutorial can recommend? The SQL can play so slip

Nothing cattle, work at ordinary times and these do not have what relation, BBS to soak out the