• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

SQL Unpivot Query

I think I need to use an UNPIVOT query to get the result set that I need.  I have a table that contacts 1 item class and many account indexes per row.  I want to change that to many item classes and 1 account index per row.  Example.

TABLE NAME: IV40400
------------------------------------------------------------
ITMCLSCD     IVIVINDX     IVIVOFIX     IVCOGSIX
ABC              10                351             20
XYZ              11                352             99


What I would like to display from the table above is the following:

ITMCLSCD     ACCOUNT                 IDX
ABC              IVIVINDX                    10
ABC              IVIVOFIX                    351
ABC              IVCOGSIX                  20
XYZ              IVIVINDX                    11
XYZ              IVIVOFIX                    352
XYZ              IVCOGSIX                  99

Now to throw one more loop, I would like to then finish that unpivot with a join to join the IDX column with the table GL00100.  Below I am attaching the query for the unpivot that I cannot get working.  I am then going to attach below that query the pseudo code for the join.
UNPIVOT Query 
-------------------------
select itmclscd, account, idx from (select itmclscd, ivivindx, ivivofix, ivcogsix from iv40400) pvt
unpivot(account for idx in ivivindx, ivivofix, ivcogsix)) as unpvt


With Pseudo Code for JOIN of GL00100
-------------------------------------
select itmclscd, account, idx from (select itmclscd, ivivindx, ivivofix, ivcogsix from iv40400) pvt
unpivot(account for idx in ivivindx, ivivofix, ivcogsix)) as unpvt
left join gl00100 b
on unpvt.idx=b.acctindx

Open in new window

0
r270ba
Asked:
r270ba
  • 6
  • 5
  • 2
2 Solutions
 
lofCommented:
here you go

isn't it amazing how much you can achieve with CTE?
with unpivoted as (
	select itmclscd, account, idx from (select itmclscd, ivivindx, ivivofix, ivcogsix from iv40400) pvt
	unpivot(account for idx in (ivivindx, ivivofix, ivcogsix)) as unpvt
) 
select *
from unpivoted
left join gl00100 b
on unpivoted.idx=b.acctindx

Open in new window

0
 
r270baAuthor Commented:
I wish I understood CTE...

I did get an error when I ran that:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'ivivindx' to data type int.
0
 
lofCommented:
oh, it means that probably not all of the columns share the same data type. check it please. or send me the create statements for tables involved and I'll have a look
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
r270baAuthor Commented:
Here is the Create statement for the GL00100 table
GL00100
--------------------------------------
USE [TSP]
GO
/****** Object:  Table [dbo].[GL00100]    Script Date: 01/12/2010 15:49:51 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[GL00100](
	[ACTINDX] [int] NOT NULL,
	[ACTNUMBR_1] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ACTNUMBR_2] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ACTNUMBR_3] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ACTALIAS] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MNACSGMT] [char](67) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ACCTTYPE] [smallint] NOT NULL,
	[ACTDESCR] [char](51) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PSTNGTYP] [smallint] NOT NULL,
	[ACCATNUM] [smallint] NOT NULL,
	[ACTIVE] [tinyint] NOT NULL,
	[TPCLBLNC] [smallint] NOT NULL,
	[DECPLACS] [smallint] NOT NULL,
	[FXDORVAR] [smallint] NOT NULL,
	[BALFRCLC] [smallint] NOT NULL,
	[DSPLKUPS] [binary](4) NOT NULL,
	[CNVRMTHD] [smallint] NOT NULL,
	[HSTRCLRT] [numeric](19, 7) NOT NULL,
	[NOTEINDX] [numeric](19, 5) NOT NULL,
	[CREATDDT] [datetime] NOT NULL,
	[MODIFDT] [datetime] NOT NULL,
	[USERDEF1] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[USERDEF2] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PostSlsIn] [smallint] NOT NULL,
	[PostIvIn] [smallint] NOT NULL,
	[PostPurchIn] [smallint] NOT NULL,
	[PostPRIn] [smallint] NOT NULL,
	[ADJINFL] [tinyint] NOT NULL,
	[INFLAREV] [int] NOT NULL,
	[INFLAEQU] [int] NOT NULL,
	[ACCTENTR] [tinyint] NOT NULL,
	[USRDEFS1] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[USRDEFS2] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKGL00100] PRIMARY KEY NONCLUSTERED 
(
	[ACTINDX] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[ACTINDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[ACTNUMBR_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[ACTNUMBR_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[ACTNUMBR_3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[ACTALIAS]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[MNACSGMT]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[ACCTTYPE]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[ACTDESCR]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[PSTNGTYP]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[ACCATNUM]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[ACTIVE]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[TPCLBLNC]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[DECPLACS]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[FXDORVAR]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[BALFRCLC]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[DSPLKUPS]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[CNVRMTHD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[GL00100].[HSTRCLRT]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[GL00100].[NOTEINDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[GL00100].[CREATDDT]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[GL00100].[MODIFDT]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[USERDEF1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[USERDEF2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[PostSlsIn]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[PostIvIn]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[PostPurchIn]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[PostPRIn]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[ADJINFL]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[INFLAREV]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[INFLAEQU]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[GL00100].[ACCTENTR]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[USRDEFS1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[GL00100].[USRDEFS2]' , @futureonly='futureonly'
GO
USE [TSP]
GO
ALTER TABLE [dbo].[GL00100]  WITH CHECK ADD CHECK  ((datepart(hour,[CREATDDT])=(0) AND datepart(minute,[CREATDDT])=(0) AND datepart(second,[CREATDDT])=(0) AND datepart(millisecond,[CREATDDT])=(0)))
GO
ALTER TABLE [dbo].[GL00100]  WITH CHECK ADD CHECK  ((datepart(hour,[MODIFDT])=(0) AND datepart(minute,[MODIFDT])=(0) AND datepart(second,[MODIFDT])=(0) AND datepart(millisecond,[MODIFDT])=(0)))

Open in new window

0
 
r270baAuthor Commented:
Here is the Create statement for the IV40400 table
USE [TSP]
GO
/****** Object:  Table [dbo].[IV40400]    Script Date: 01/12/2010 15:50:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[IV40400](
	[ITMCLSCD] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ITMCLSDC] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DEFLTCLS] [tinyint] NOT NULL,
	[NOTEINDX] [numeric](19, 5) NOT NULL,
	[ITEMTYPE] [smallint] NOT NULL,
	[ITMTRKOP] [smallint] NOT NULL,
	[LOTTYPE] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[KPERHIST] [tinyint] NOT NULL,
	[KPTRXHST] [tinyint] NOT NULL,
	[KPCALHST] [tinyint] NOT NULL,
	[KPDSTHST] [tinyint] NOT NULL,
	[ALWBKORD] [tinyint] NOT NULL,
	[ITMGEDSC] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TAXOPTNS] [smallint] NOT NULL,
	[ITMTSHID] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Purchase_Tax_Options] [smallint] NOT NULL,
	[Purchase_Item_Tax_Schedu] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[UOMSCHDL] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[VCTNMTHD] [smallint] NOT NULL,
	[USCATVLS_1] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[USCATVLS_2] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[USCATVLS_3] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[USCATVLS_4] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[USCATVLS_5] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[USCATVLS_6] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DECPLQTY] [smallint] NOT NULL,
	[IVIVINDX] [int] NOT NULL,
	[IVIVOFIX] [int] NOT NULL,
	[IVCOGSIX] [int] NOT NULL,
	[IVSLSIDX] [int] NOT NULL,
	[IVSLDSIX] [int] NOT NULL,
	[IVSLRNIX] [int] NOT NULL,
	[IVINUSIX] [int] NOT NULL,
	[IVINSVIX] [int] NOT NULL,
	[IVDMGIDX] [int] NOT NULL,
	[IVVARIDX] [int] NOT NULL,
	[DPSHPIDX] [int] NOT NULL,
	[PURPVIDX] [int] NOT NULL,
	[UPPVIDX] [int] NOT NULL,
	[IVRETIDX] [int] NOT NULL,
	[ASMVRIDX] [int] NOT NULL,
	[PRCLEVEL] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PriceGroup] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PRICMTHD] [smallint] NOT NULL,
	[TCC] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Revalue_Inventory] [tinyint] NOT NULL,
	[Tolerance_Percentage] [int] NOT NULL,
	[CNTRYORGN] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[STTSTCLVLPRCNTG] [smallint] NOT NULL,
	[INCLUDEINDP] [tinyint] NOT NULL,
	[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKIV40400] PRIMARY KEY NONCLUSTERED 
(
	[ITMCLSCD] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[ITMCLSCD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[ITMCLSDC]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[DEFLTCLS]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[IV40400].[NOTEINDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[ITEMTYPE]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[ITMTRKOP]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[LOTTYPE]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[KPERHIST]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[KPTRXHST]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[KPCALHST]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[KPDSTHST]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[ALWBKORD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[ITMGEDSC]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[TAXOPTNS]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[ITMTSHID]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[Purchase_Tax_Options]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[Purchase_Item_Tax_Schedu]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[UOMSCHDL]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[VCTNMTHD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[USCATVLS_1]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[USCATVLS_2]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[USCATVLS_3]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[USCATVLS_4]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[USCATVLS_5]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[USCATVLS_6]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[DECPLQTY]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVIVINDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVIVOFIX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVCOGSIX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVSLSIDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVSLDSIX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVSLRNIX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVINUSIX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVINSVIX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVDMGIDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVVARIDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[DPSHPIDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[PURPVIDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[UPPVIDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[IVRETIDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[ASMVRIDX]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[PRCLEVEL]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[PriceGroup]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[PRICMTHD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[TCC]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[Revalue_Inventory]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[Tolerance_Percentage]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[IV40400].[CNTRYORGN]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[STTSTCLVLPRCNTG]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[IV40400].[INCLUDEINDP]' , @futureonly='futureonly'

Open in new window

0
 
lofCommented:
i found one type on the column name but the query is working fine here. I simplified it a bit as well. it looks like there is some autocasting going on in the background

try it again and copy any error messages


with unpivoted as (
	select itmclscd, account, idx from iv40400 pvt
	unpivot(account for idx in (ivivindx, ivivofix, ivcogsix)) as unpvt
) 
select unpivoted.*
from unpivoted
left join gl00100 b
on unpivoted.idx=b.actindx

Open in new window

0
 
r270baAuthor Commented:
That executed without error that time.  However, when I add any columns from the GL00100 join, I get the same error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'IVIVINDX' to data type int.

See the modified query below.
with unpivoted as (
	select itmclscd, account, idx from iv40400 pvt
	unpivot(account for idx in (ivivindx, ivivofix, ivcogsix)) as unpvt
) 
select unpivoted.*, b.actindx
from unpivoted
left join gl00100 b
on unpivoted.idx=b.actindx

Open in new window

0
 
lofCommented:
it must be something in the data. I've just run your updated query without any errors, but I have only the structure.

ok, let's get back to your longer version and add explicit conversion



with unpivoted as (
	select itmclscd, account, idx from (select itmclscd, convert(varchar(32),ivivindx) ivivindx, convert(varchar(32),ivivofix) ivivofix, convert(varchar(32),ivcogsix) ivcogsix from iv40400) pvt
	unpivot(account for idx in (ivivindx, ivivofix, ivcogsix)) as unpvt
) 
select unpivoted.*
from unpivoted
left join gl00100 b
on unpivoted.idx=b.actindx

Open in new window

0
 
r270baAuthor Commented:
I am still getting the same error.  Now the query you posted works just fine.  It is only when I add the b.actindx from the GL00100 join.  That appears to be what is throwing the error (even with the explicit conversion)...  Any more ideas?
0
 
lofCommented:
no idea why it happens. Anybody else out there? Speak up you mighty experts :)
0
 
ralmadaCommented:
Your problem is that you should join the tables with the "account" column not the "idx" one, because the idx one contains the header of your original table that is ("ivivindx, "ivivofix", "ivcogsix").
So basically your query results in this:


itmclscd account idx
ABC 10 IVIVINDX
ABC 351 IVIVOFIX
ABC 20 IVCOGSIX
XYZ 11 IVIVINDX
XYZ 352 IVIVOFIX
XYZ 99 IVCOGSIX
 

;with CTE as (
select itmclscd, account, idx from @iv40400 pvt
	unpivot(account for idx in (ivivindx, ivivofix, ivcogsix)) as unpvt
) 
select * from CTE a
left join gl00100 b
on a.account=b.actindx

Open in new window

0
 
ralmadaCommented:
If you want the account number to be in the idx column then you should invert them in your unpivot section like this:
;with CTE as (
select itmclscd, account, idx from @iv40400 pvt
	unpivot(idx for account in (ivivindx, ivivofix, ivcogsix)) as unpvt
) 
select * from CTE a
left join gl00100 b
on a.idx=b.actindx

Open in new window

0
 
r270baAuthor Commented:
I am splitting the points and giving LOF the majority since he spent most of the time working on it.  RALMADA actually fixed the problem that LOF's query was having and solved the answer.

I truly appreciate all of your hard work on this LOF and RALMADA!  I cannot say enough how much Experts-Exchange and it's Experts mean to me in my daily job!

If there are any disagreements on how I split the points please speak up.  I awarded the best I could.

Ron
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now