rows to columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

rows to columns

Author

Topic

joshcas
Starting Member

5 Posts

Posted - 2014-07-22 : 12:32:12

Hi, Good Morning

I'm really new to sql and I've been working on a query to change rows to columns , but I'm getting an error in the from clause :( , is there something that I'm not seeing ?

I'm writing the query to display the data like this:

declare @period_list as varchar(max)


select @period_list =

      stuff(( select distinct ',' + convert(varchar(10),Convert(date,SNP.SEC_NON_SEC_POSN_PRCS_DT)) 


                  FROM [dbo].[RZ_SEC_NON_SEC_POSN_F] SNP
            

For xml path('')),1,1,'')

declare @dynamic_pivot_query as varchar(max)
set @dynamic_pivot_query =
'select [SNP.SO_ID],'+
@period_list+
'From
(
SELECT SNP.SO_ID
,SNP.SEC_NON_SEC_POSN_PRCS_DT
,SNP.CP_SEC_POSN_NET_USD_AM
FROM [dbo].[RZ_SEC_NON_SEC_POSN_F] SNP
) as S
Pivot
(
SUM (SNP.CP_SEC_POSN_NET_USD_AM)
FROM SNP.SEC_NON_SEC_POSN_PRCS_DT in ('+@period_list+')
) as p
'
exec (@dynamic_pivot_query)

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-22 : 13:34:22

The values must be [7/16/2014],[7/18/2014],ETC

joshcas
Starting Member

5 Posts

Posted - 2014-07-22 : 13:41:13

Not sure that I understood your answer

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-22 : 13:47:06

I found a few other issues. You referenced your alias out side the inline query ..

Try the following - I created a local table and tested - it worked for me

declare @period_list as varchar(max)

select @period_list =
stuff(( select distinct ',[' + convert(varchar(10),Convert(date,SNP.SEC_NON_SEC_POSN_PRCS_DT) ) + ']'
FROM RZ_SEC_NON_SEC_POSN_F SNP

For xml path('')),1,1,'')

declare @dynamic_pivot_query as varchar(max)
set @dynamic_pivot_query =
'select [SO_ID],'+
@period_list+
'From
(
SELECT SNP.SO_ID
,SNP.SEC_NON_SEC_POSN_PRCS_DT
,SNP.CP_SEC_POSN_NET_USD_AM
FROM #RZ_SEC_NON_SEC_POSN_F SNP
) as S
Pivot
(
SUM (CP_SEC_POSN_NET_USD_AM)
FOR SEC_NON_SEC_POSN_PRCS_DT in ('+@period_list+')
) as p
'

exec (@dynamic_pivot_query)

if you want it fully aggregated
set @dynamic_pivot_query =
'select '+
@period_list+
'From
(
SELECT
SNP.SEC_NON_SEC_POSN_PRCS_DT
,SNP.CP_SEC_POSN_NET_USD_AM
FROM #RZ_SEC_NON_SEC_POSN_F SNP
) as S
Pivot
(
SUM (CP_SEC_POSN_NET_USD_AM)
FOR SEC_NON_SEC_POSN_PRCS_DT in ('+@period_list+')
) as p
'

joshcas
Starting Member

5 Posts

Posted - 2014-07-22 : 16:22:22

that was brilliant !!!, hats off to you Sir

joshcas
Starting Member

5 Posts

Posted - 2014-07-22 : 17:17:45

Just one more thing , I'm getting a few errors in my "Where" clause , and I know they are valid because I use them in another query

declare @period_list as varchar(max)

select @period_list =
stuff(( select distinct ',[' + convert(varchar(10),Convert(date,SNP.SEC_NON_SEC_POSN_PRCS_DT) ) + ']'
FROM RZ_SEC_NON_SEC_POSN_F SNP
LEFT JOIN RZ_DT_DAY_D RRD ON SNP.REV_RECGN_DT = RRD.CLDR_DT
LEFT JOIN RZ_DT_DAY_D ERRD ON SNP.ESTMT_REV_RECGN_DT = ERRD.CLDR_DT
LEFT JOIN RZ_BUS_AREA_D BABAD ON SNP.BUS_AREA_CD = BABAD.BUS_AREA_CD
LEFT JOIN RZ_PRFT_CTR_D PCPCD ON SNP.PRFT_CTR_CD = PCPCD.PRFT_CTR_CD
LEFT JOIN RZ_SRC_SYS_D SOSSD ON SNP.SO_SRC_SYS_KY = SOSSD.SRC_SYS_KY
LEFT JOIN RZ_SEC_NON_SEC_POSN_D SNSPDQSNSPD ON SNP.QTRLY_SEC_NON_SEC_POSN_MEMB_ID = SNSPDQSNSPD.SEC_NON_SEC_POSN_MEMB_ID
LEFT JOIN RZ_CUST_HIER_D STCHD_1 ON SNP.SLDT_CUST_ID = STCHD_1.CUST_ID
LEFT JOIN RZ_CUST_HIER_D STCHD_2 ON SNP.SHPT_CUST_ID = STCHD_2.CUST_ID
LEFT JOIN RZ_FISC_CLDR_D FCM ON SNP.SEC_NON_SEC_POSN_PRCS_DT=FCM.FISC_CLDR_DT
LEFT JOIN IC_CATG_REF CTG ON SNP.BUS_AREA_CD = CTG.BUS_AREA_CD
LEFT JOIN RZ_PROD_HIER_D PEPD ON SNP.PROD_ID = PEPD.PROD_ID
LEFT JOIN RZ_SLS_CHNL_D SCSCD ON SNP.SLS_CHNL_CD = SCSCD.SLS_CHNL_CD
LEFT JOIN RZ_ACCT_MGMT_D AMID ON SNP.CP_AMID_LVL_2_ID = AMID.AMID_LVL_2_ID
LEFT JOIN RZ_BUS_PROD_D SKU ON SNP.PROD_ID = SKU.PROD_ID
INNER JOIN RZ_DT_DAY_D CURR_QTR ON SNP.SEC_NON_SEC_POSN_PRCS_DT = CURR_QTR.CLDR_DT

WHERE
((BABAD.GBL_BUS_UNIT_NM IN ('Servers','Networking','Storage Division')) AND
(SNP.INT_PRCS_CD IN ('REGULAR')) AND
(SOSSD.SRC_SYS_KY <> '93') AND
(SNP.CP_AMID_LVL_2_ID NOT IN ('?','AUDEFAULT')) AND
(SNP.SEC_NON_SEC_POSN_PRCS_DT >= (SELECT DATEADD(DAY,-8,PRCS_DT) FROM IC_RLD_PARM where REC_TYPE='SNP'))
AND RTRIM (case
when (SNP.CP_REV_RECGN_CATG IN ('UDO')) then 'Opportunity'
when (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_4_DN = 'Revenues ' OR SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_4_DN is null OR SNP.INT_PRCS_CD = 'DLY$_ADJ' OR SNP.INT_PRCS_CD = 'MANUAL_ADJ') then 'Revenue'
when (SNP.CP_REV_RECGN_CATG IN ('USC','SSC')) then 'Secured'
when (SNP.CP_REV_RECGN_CATG IN ('ULD','SLD')) then 'Load'
when (SNP.CP_REV_RECGN_CATG IN ('UOP','SOP')) then 'Opportunity'
when ((SNP.CP_REV_RECGN_CATG IN ('Include','INCLUDE','include')) AND (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN = 'Secure Position' OR SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN is null)) then 'Load'
when (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN = 'Secure Position' OR SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN is null) then 'Secured'
when (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN = 'Non Secure Potential ') then 'Load'
when (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN = 'Non Secure No Potential ') then 'Opportunity'
End) <> 'Revenue' AND
(SNP.CP_REV_RECGN_CATG = 'Include'))

For xml path('')),1,1,'')

declare @dynamic_pivot_query as varchar(max)
set @dynamic_pivot_query =
'select [SO_ID],'+
@period_list+
'From
(
SELECT SNP.SO_ID
,SNP.SEC_NON_SEC_POSN_PRCS_DT
,SNP.CP_SEC_POSN_NET_USD_AM
FROM RZ_SEC_NON_SEC_POSN_F SNP
LEFT JOIN RZ_DT_DAY_D RRD ON SNP.REV_RECGN_DT = RRD.CLDR_DT
LEFT JOIN RZ_DT_DAY_D ERRD ON SNP.ESTMT_REV_RECGN_DT = ERRD.CLDR_DT
LEFT JOIN RZ_BUS_AREA_D BABAD ON SNP.BUS_AREA_CD = BABAD.BUS_AREA_CD
LEFT JOIN RZ_PRFT_CTR_D PCPCD ON SNP.PRFT_CTR_CD = PCPCD.PRFT_CTR_CD
LEFT JOIN RZ_SRC_SYS_D SOSSD ON SNP.SO_SRC_SYS_KY = SOSSD.SRC_SYS_KY
LEFT JOIN RZ_SEC_NON_SEC_POSN_D SNSPDQSNSPD ON SNP.QTRLY_SEC_NON_SEC_POSN_MEMB_ID = SNSPDQSNSPD.SEC_NON_SEC_POSN_MEMB_ID
LEFT JOIN RZ_CUST_HIER_D STCHD_1 ON SNP.SLDT_CUST_ID = STCHD_1.CUST_ID
LEFT JOIN RZ_CUST_HIER_D STCHD_2 ON SNP.SHPT_CUST_ID = STCHD_2.CUST_ID
LEFT JOIN RZ_FISC_CLDR_D FCM ON SNP.SEC_NON_SEC_POSN_PRCS_DT=FCM.FISC_CLDR_DT
LEFT JOIN IC_CATG_REF CTG ON SNP.BUS_AREA_CD = CTG.BUS_AREA_CD
LEFT JOIN RZ_PROD_HIER_D PEPD ON SNP.PROD_ID = PEPD.PROD_ID
LEFT JOIN RZ_SLS_CHNL_D SCSCD ON SNP.SLS_CHNL_CD = SCSCD.SLS_CHNL_CD
LEFT JOIN RZ_ACCT_MGMT_D AMID ON SNP.CP_AMID_LVL_2_ID = AMID.AMID_LVL_2_ID
LEFT JOIN RZ_BUS_PROD_D SKU ON SNP.PROD_ID = SKU.PROD_ID
INNER JOIN RZ_DT_DAY_D CURR_QTR ON SNP.SEC_NON_SEC_POSN_PRCS_DT = CURR_QTR.CLDR_DT
WHERE
((BABAD.GBL_BUS_UNIT_NM IN ("Servers","Networking","Storage Division")) AND
(SNP.INT_PRCS_CD IN ("REGULAR")) AND
(SOSSD.SRC_SYS_KY <> "93") AND
(SNP.CP_AMID_LVL_2_ID NOT IN ("?","AUDEFAULT")) AND
(SNP.SEC_NON_SEC_POSN_PRCS_DT >= (SELECT DATEADD(DAY,-8,PRCS_DT) FROM IC_RLD_PARM where REC_TYPE="SNP"))
AND RTRIM (case
when (SNP.CP_REV_RECGN_CATG IN ("UDO")) then "Opportunity"
when (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_4_DN = "Revenues " OR SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_4_DN is null OR SNP.INT_PRCS_CD = "DLY$_ADJ" OR SNP.INT_PRCS_CD = "MANUAL_ADJ") then "Revenue"
when (SNP.CP_REV_RECGN_CATG IN ("USC","SSC")) then "Secured"
when (SNP.CP_REV_RECGN_CATG IN ("ULD","SLD")) then "Load"
when (SNP.CP_REV_RECGN_CATG IN ("UOP","SOP")) then "Opportunity"
when ((SNP.CP_REV_RECGN_CATG IN ("Include","INCLUDE","include")) AND (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN = "Secure Position" OR SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN is null)) then "Load"
when (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN = "Secure Position" OR SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN is null) then "Secured"
when (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN = "Non Secure Potential ") then "Load"
when (SNSPDQSNSPD.SEC_NON_SEC_POSN_LVL_2_DN = "Non Secure No Potential ") then "Opportunity"
End) <> "Revenue" AND
(SNP.CP_REV_RECGN_CATG = "Include"))
) as S
Pivot
(
SUM (CP_SEC_POSN_NET_USD_AM)
FOR SEC_NON_SEC_POSN_PRCS_DT in ('+@period_list+')
) as p
'

exec (@dynamic_pivot_query)

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-22 : 20:11:16

If you are doing this is SQL Server - you need to Change " to ' on all your strings. That sticks out first

It should be something like this example when you are using string values. Run the following.

SELECT ' WHERE Color IN (''RED'',''YELLOW'',''BLUE'')'

joshcas
Starting Member

5 Posts

Posted - 2014-07-23 : 14:27:41

Hi Michael , thanks again for the follow up in this matter

I didn't quite get your instruccions , because I’m just getting started with SQL queries , Would you give me a hand publishing, how the code above would look like with the "Where" filters working ?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-24 : 09:46:44

You are using double quote in your SQL for your string literals: you need to replace with multiple single quotes. Example The first is an extract from your SQL the latter is how it should be: run the following.

SELECT '(SNP.CP_AMID_LVL_2_ID NOT IN ("?","AUDEFAULT")) AND'
SELECT '(SNP.CP_AMID_LVL_2_ID NOT IN (''?'',''AUDEFAULT'')) AND'

krish123
Starting Member

3 Posts

Posted - 2014-07-28 : 09:53:05

Need a SQL QUERY ONLY.....
I HAVE THE BELOW DATA

EXCEL SHEET DATA

EID HCL WIPRO ---EXCEL COLIMNS NAMES
101 No YES
101 No YES
102 YES YES
102 No YES
103 No No
103 No No

Table stucture

EMID CODE COMPANY Dsc
101 YES HCL HE is WORKING IN HCL
101 workdone null
101 YES WIPRO HE is WORKING IN WIPRO
102 NO HCL HE is NOT WORKING IN HCL
102 passed null
102 YES WIPRO HE is WORKING IN WIPRO
103 pink

I need to UPDATE/INSERT the Table above by using above excel sheet data.if u have any doubt plz ask me b4good2012@GMail
Quick response will highly appreciable -

Thanks,
Krish

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-28 : 09:58:44

Insert and update from the excel?

You could just import the data into SQL Server stage table and update via a script. This can be done in SSIS.

Do you care how you insert update? if you update from excel you will need to use "openRowSet"

krish123
Starting Member

3 Posts

Posted - 2014-07-28 : 10:06:39

Michael,

I think you have not understood, here columns excel columns and table coulms are diffrent .

Thanks,
Krish

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-28 : 10:45:57

Perhaps, but I think you might misunderstand me.

A stage table approach would consist of a table that would mimic your excel structure. You would upload excel to that table. You would the issues a sql statement to update\insert the appropriate data in your sql server target table.

alternatively, you can update or insert directly from excel using openrowset. You open the excel via t-sql insert or update directly from the excel to the target sql server table.

or am I way off ( I am indeed misunderstanding you) and you just want some insert and update scripts based on the data you have in this thread - meaning you are not interested in a repeatable process and you just want a few insert update scripts.

krish123
Starting Member

3 Posts

Posted - 2014-07-28 : 11:41:47

Thanks Michael for the follow up in this matter.
I think i am not getting your views properly.
It is not that much easy for me.
can u confirm is it possible to solve my question.

I will clarify you my doubt here ..plz read carefully,

EXCEL SHEET DATA

EID HCL WIPRO----columns in excel sheet

101 No YES
101 No YES
102 YES YES
102 No YES
103 No No
103 No No

Table stucture

EMID CODE COMPANY Dsc

101 YES HCL HE is WORKING IN HCL
101 workdone null -
101 YES WIPRO HE is WORKING IN WIPRO
102 NO HCL HE is NOT WORKING IN HCL
102 passed null -
102 YES WIPRO HE is WORKING IN WIPRO
103 pink - -

I need to do the following things,
1) Need check in the sql table whether given EMID having row values as HCL,WIPRO according to excel data(BY USING EMPID).
2)If no then need to insert the data according to excel sheet
3)if yes then need to update existing data using excel data.
plz let me know if u required further assistance here

NOTE: HERE excel fields and sql table fields are different and also one another field(DSC) is there in sql table this field value depends on
Another field (CODE).

Thanks,
Krish

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-28 : 12:40:41

I am having no problem understanding that you need to update data in your table with data in excel. are you doing this once or will there be subsequent spreadsheets that you will also need to use to update the table?

Regardless: my recommendation is to import your excel data into a table that mimics the structure of your excel

-- all data types and sizes are a guess
CREATE TABLE #X
(
EID int ,
HCL varchar(10),
WIPRO varchar(100)
)

INSERT INTO #X
VALUES(101,'Yes','Not Working'),(102,'Yes','Not Working')

CREATE TABLE #StageExcel
(
EMID Int ,
CODE Varchar(10),
COMPANY varchar(100),
Dsc varchar(100)
)

INSERT INTO #StageExcel
VALUES(103,'Yes','CompanyA','New to Working'),(101,'Yes','CompanyB','Kind of Working')

SELECT * FROM #X m-- this wold be the table in the db
SELECT * FROM #StageExcel m-- this wold be the table you will create and upload your Excel data into

--Then you run the following - I am not sure on which fields map to which, so this is an example and you have more fields in your source than your target

MERGE #X Target
USING #StageExcel Source
ON Target.EID = EMID
WHEN MATCHED THEN UPDATE
SET HCL = Source.CODE
,WIPRO = Dsc
WHEN NOT MATCHED THEN INSERT
(EID,HCL,WIPRO)
VALUES(Source.EMID,Source.CODE,Source.Dsc);

SELECT * FROM #X -- notice EID 101 is updated and EID103 is added

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources