?
Solved

SQL Unpivot Query

Posted on 2010-01-12
13
Medium Priority
?
675 Views
Last Modified: 2012-05-08
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
Comment
Question by:r270ba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 10

Expert Comment

by:lof
ID: 26297284
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
 

Author Comment

by:r270ba
ID: 26297325
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
 
LVL 10

Expert Comment

by:lof
ID: 26297483
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
A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

 

Author Comment

by:r270ba
ID: 26297501
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
 

Author Comment

by:r270ba
ID: 26297511
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
 
LVL 10

Expert Comment

by:lof
ID: 26297709
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
 

Author Comment

by:r270ba
ID: 26297731
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
 
LVL 10

Expert Comment

by:lof
ID: 26297827
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
 

Author Comment

by:r270ba
ID: 26297848
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
 
LVL 10

Accepted Solution

by:
lof earned 1400 total points
ID: 26297975
no idea why it happens. Anybody else out there? Speak up you mighty experts :)
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 600 total points
ID: 26298817
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
 
LVL 41

Expert Comment

by:ralmada
ID: 26298845
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
 

Author Closing Comment

by:r270ba
ID: 31676306
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question