August 27, 2014 at 12:59 am
Hi,
I have a table with two columns like this
id_Contractnm_ContractType cd_StaffType
1 CDI Internal
2 CDI ALD PAID Internal
3 CDD Internal
4 CONSULTANT External
5 TRAINEE External
6 TEMPORARY WORKER External
7 SUMMER JOB External
and i want result like this-
Internal
CDI
CDI ALD PAID
CDD
External
CONSULTANT
TRAINEE
TEMPORARY WORKER
SUMMER JOB
Please help
August 27, 2014 at 2:16 am
Something like this?
😎
DECLARE @SAMPLE TABLE
(
id_Contract INT NOT NULL
,nm_ContractType VARCHAR(25) NOT NULL
,cd_StaffType VARCHAR(15) NOT NULL
);
INSERT INTO @SAMPLE (id_Contract,nm_ContractType,cd_StaffType)
VALUES
(1,'CDI' ,'Internal')
,(2,'CDI ALD PAID' ,'Internal')
,(3,'CDD' ,'Internal')
,(4,'CONSULTANT' ,'External')
,(5,'TRAINEE' ,'External')
,(6,'TEMPORARY WORKER' ,'External')
,(7,'SUMMER JOB' ,'External');
SELECT 'Internal' AS CONTRACT_TYPE
UNION ALL
SELECT
nm_ContractType
FROM @SAMPLE S
WHERE S.cd_StaffType = 'Internal'
UNION ALL
SELECT 'External' AS CONTRACT_TYPE
UNION ALL
SELECT
nm_ContractType
FROM @SAMPLE S
WHERE S.cd_StaffType = 'External';
Results
CONTRACT_TYPE
---------------
Internal
CDI
CDI ALD PAID
CDD
External
CONSULTANT
TRAINEE
TEMPORARY WORKER
SUMMER JOB
August 28, 2014 at 2:30 am
Thanks for your reply. but the problem is that these External and Internal Columns are not fixed means they are dynamic. moreover the result should be like in shoring order. please see the result like this-
1. Internal and external should be in shorting order
2. inside interal and external the column should be in shorted order
External
CONSULTANT
Internal
August 28, 2014 at 2:32 am
Thanks for your reply. but the problem is that these External and Internal Columns are not fixed means they are dynamic. moreover the result should be like in shoring order. please see the result like this-
1. Internal and external should be in shorting order
2. inside interal and external the column should be in shorted order
External
CONSULTANT
SUMMER JOB
TEMPORARY WORKER
TRAINEE
Internal
CDD
CDI
CDI ALD PAID
hope its clear now.
August 28, 2014 at 8:24 am
does this work for you?
DECLARE @SAMPLE TABLE
(
id_Contract INT NOT NULL
,nm_ContractType VARCHAR(25) NOT NULL
,cd_StaffType VARCHAR(15) NOT NULL
);
INSERT INTO @SAMPLE (id_Contract,nm_ContractType,cd_StaffType)
VALUES
(1,'CDI' ,'Internal')
,(2,'CDI ALD PAID' ,'Internal')
,(3,'CDD' ,'Internal')
,(4,'CONSULTANT' ,'External')
,(5,'TRAINEE' ,'External')
,(6,'TEMPORARY WORKER' ,'External')
,(7,'SUMMER JOB' ,'External')
,(8,'IT' ,'Outsourced')
,(9,'OFFICE CLEANERS' ,'Outsourced')
,(10,'STORES' ,'Auxiliary')
,(11,'DRIVERS' ,'Auxiliary')
;
WITH ctesort
AS (
SELECT
cd_StaffType
, nm_ContractType
, DENSE_RANK( ) OVER (ORDER BY cd_stafftype) * 100
+ ROW_NUMBER() OVER (PARTITION BY cd_stafftype ORDER BY nm_contracttype) AS so /* allows for 100 Contract Types per Staff type */
FROM @sample
)
SELECT
CONTRACT_TYPE
FROM(
SELECT
cd_StaffType AS CONTRACT_TYPE
, MIN(so) - 1 AS sortorder
FROM ctesort
GROUP BY cd_StaffType
UNION ALL
SELECT
nm_ContractType
, so
FROM ctesort) x
ORDER BY
sortorder;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply