November 5, 2014 at 9:40 pm
Detail:
-------
create table detail
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)
insert into detail values ('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
values ('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
values ('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50')
here my expecting o/p:
-----------
i wanna display ED ,CESS ,HCESS,VAT in seperate columns in output How to that?
November 5, 2014 at 10:36 pm
From your description and table definition, I have no clue how to get what you want. What's the logic to accomplish that? To me at least, there's no obvious connection between what your description and the solution.
November 5, 2014 at 11:35 pm
Declare @detail table
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)
insert into @detail
values ('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
,('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
,('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
,('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
, ('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50')
Select grn_no, po_no, [ED12], [CST], [CES6], [VAT], [HCES2]
from
(
select grn_no, po_no, Formul_id, adjus_value
from @detail
) T1
Pivot (max(adjus_value) for Formul_id in ([ED12],[CST],[CES6],[VAT],[HCES2]))t2
November 6, 2014 at 1:51 am
You might find this article quite interesting as well,http://www.sqlservercentral.com/articles/T-SQL/63681/ as it also shows the performance of the PIVOT and the more traditional Cross Tab method, especially over larger data sets.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 6, 2014 at 1:59 am
Quick crosstab as an alternative to the pivot
😎
USE tempdb;
GO
SET NOCOUNT ON;
Declare @detail table
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)
insert into @detail
values
('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
,('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
,('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
,('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
,('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50');
SELECT
DT.grn_no
,DT.po_no
,DT.vendor_no
,MAX(CASE WHEN DT.Formul_id = 'ED12' THEN DT.adjus_value END) AS ED12
,MAX(CASE WHEN DT.Formul_id = 'CST' THEN DT.adjus_value END) AS CST
,MAX(CASE WHEN DT.Formul_id = 'CES6' THEN DT.adjus_value END) AS CES6
,MAX(CASE WHEN DT.Formul_id = 'VAT' THEN DT.adjus_value END) AS VAT
,MAX(CASE WHEN DT.Formul_id = 'HCES2' THEN DT.adjus_value END) AS HCES2
,MAX(CASE WHEN DT.Formul_id = 'ED12' THEN DT.Adjust_code END) AS AC_ED12
,MAX(CASE WHEN DT.Formul_id = 'CST' THEN DT.Adjust_code END) AS AC_CST
,MAX(CASE WHEN DT.Formul_id = 'CES6' THEN DT.Adjust_code END) AS AC_CES6
,MAX(CASE WHEN DT.Formul_id = 'VAT' THEN DT.Adjust_code END) AS AC_VAT
,MAX(CASE WHEN DT.Formul_id = 'HCES2' THEN DT.Adjust_code END) AS AC_HCES2
FROM @detail DT
GROUP BY DT.grn_no
,DT.po_no
,DT.vendor_no;
Results
grn_no po_no vendor_no ED12 CST CES6 VAT HCES2 AC_ED12 AC_CST AC_CES6 AC_VAT AC_HCES2
--------------- ---------------- ---------- ----- ---- ----- ---- ------ -------- ------- -------- ------- ---------
Grn/0001/14-15 po/00011/14-15/ 5000.00 500 50 5 0 2.5 ED12 CST12 CES6 VAT HCES2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply