April 15, 2019 at 2:15 am
I have two tables,PRODUCTS AND LOOKUP TABLES.Now i want to order the KEY Column in products table based on F_KEY column value in LOOKUP TABLE
CREATE TABLE PRODUCTS
(
ID INT,
KEY VARCHAR(50)
)
INSERT INTO PRODUCTS
VALUES (1, 'EGHS'), (2, 'PFE'), (3, 'EGHS'),
(4, 'PFE'), (5, 'ABC')
CREATE TABLE LOOKUP (F_KEY VARCHAR(50))
INSERT INTO LOOKUP VALUES('PFE,EGHS,ABC')
Now I want to order the records in PRODUCTS table based on F_KEY (PFE,EGHS,ABC) values in LOOKUP table.
Example output:
PRODUCTS
ID KEY
-----------
2 PFE
4 PFE
1 EGHS
3 EGHS
5 ABC
I used below query.It is working,But i need to hard coded the KEY values in the query.Suppose if the F_KEY value is changed (or) increased in LOOKUP Table.We need to change above query also For example(PFE,EGHS,ABC,JKR),.so Is there any method or dynamic query to do this?
QUERY
SELECT ID, FROM PRODUCTS
ORDER BY
CASE
WHEN 'PFE' THEN 1
WHEN 'EGHS' THEN 2
WHEN 'ABC' THEN 3
END
April 15, 2019 at 4:04 am
Like this?
use tempdb;
GO
-- Now i want to order the KEY Column in products table
use tempdb;
GO
-- Now i want to order the KEY Column in products table
CREATE TABLE PRODUCTS
(
ID INT,
RandomValue VARCHAR(4)
);
GO
INSERT INTO PRODUCTS VALUES
(1, 'EGHS'), (2, 'PFE'), (3, 'EGHS'),(4, 'PFE'), (5,'ABC');
CREATE TABLE OtherTable (F_KEY VARCHAR(4), Seq TINYINT)
GO
INSERT INTO OtherTable VALUES ('PFE',1),('EGHS',2),('ABC',3);
SELECT p.*, ot.*
FROM Products p INNER JOIN OtherTable ot ON p.RandomValue = ot.F_KEY
ORDER BY ot.Seq;
April 15, 2019 at 12:23 pm
Thank you.It is working fine.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply