NEED HELP SQL QUERY

Question

NEED HELP SQL QUERY

Nicky james

SSC Journeyman

Points: 95
More actions
January 21, 2011 at 5:13 am

#243997

Dear all, I'm new to this site. I need help to write a query for selecting from two tables.
I have 2 tables named ; custinvoice and dims;
I need to write some select statement to bind two tables and display the result with the description concatenated to the respective dimensions. PLSSS HELP..
Table - custinvoice
NAME---DIM1--DIM2--DIM3---CMPY
TRAC111---10---101---10101---bagty
TRAC111---10---101---10101---bagty
TRAC111---10---101---10101---bagty
TRAC111---10--- 101---10101---bagty
TRAC444---10---101---10101---bagty
TRAC444---10---101---10101---bagty
TRAC111---10---101---10101---bagty
TRAC444---10---101---10101---bagty
TRAC444---10---101---10101---bagty
RES ABS---10---101---30000---bagty
RES ABS---20---200---20101---bagty
RES ABS---30---301---30000---bagty
RES ABS---20---201---20101---bagty
Table - Dims
DESCRIPTION---NUM--- CMPY--- level
Ph Department--- 10 ---bagty ---0
GG DEPT --- 20 ---bagty ---0
MT SERVICES --- 30 ---bagty ---0
Ph Sub1 Dpt --- 101 ---bagty ---1
Ph Sub2 --- 102 ---bagty ---1
MT SUB1 --- 300 ---bagty ---1
GG SUB 1 DEPT--- 200 ---bagty ---1
Ph Sub2 Dpt ---10101 ---bagty---2
Ph Sub3 Dpt ---10102 ---bagty---2
Ph Sub4 Dpt ---10103 ---bagty---2
Ph Sub5 Dpt ---10104 ---bagty---2
GG SUB 2 DEPT---201 ---bagty---1
GG SUB 3 DEPT---20101 ---bagty---2
MT SUB3 ---30000 ---bagty---2
MT SUB2 ---301 --- bagty---1
RESULT NEEDED
NAME ---DIM1 ----------- DIM --------------- DIM3 --------------- CMPY
TRAC111---10-Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt---bagty
TRAC111---10- Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt---bagty
TRAC111---10- Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt---bagty
TRAC111---10- Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt---bagty
TRAC444---10- Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt---bagty
TRAC444---10- Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt--- bagty
TRAC111---10- Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt---bagty
TRAC444---10- Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt---bagty
TRAC444---10- Ph Department---101 - Ph Sub1 Dpt---10101 - Ph Sub2 Dpt---bagty
RES ABS---10- Ph Department---101 - Ph Sub1 Dpt---30000 - MT SUB3 --- bagty
RES ABS---20- GG DEPT--- 200 - GG SUB 1 DEPT---20101 - GG SUB 3 DEPT---bagty
RES ABS---30- MT SERVICES --- 301 - MT SUB2--- 30000 - MT SUB3 --- bagty
RES ABS---20- GG DEPT--- 201 - GG SUB 2 DEPT---20101 - GG SUB 3 DEPT---bagty
RES ABS---10- Ph Department---102 - Ph Sub2 ---30000 - MT SUB3 -- - bagty

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Read this to see the best way to post this to get quick responses. http://www.sqlservercentral.com/articles/Best+Practices/61537/

Please don't start multiple threads for the same question in future. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nicky james SSC Journeyman Points: 95 More actions · Answer 2

I need help to write a query for selecting from two tables.

I have 2 tables named ; sales and dimensions;

I need to write some select statement to bind two tables and display the result with the description concatenated to the respective dimensions. PLSSS HELP..

ie the result should be like for all CMPY = baygt;

INVOICEID------ITMNAME-----DIM1-----------------DIM2---------DIM3-----------CMPY

SINV-0015------ORGMICRO----10:Ph Department-----101:AB Dpt---30000:Multi-----baygt

SINV-0019------TRACLR------20:MST-------------- 201:SCaDD----20101:LAB-------baygt

and the dimensions are concatenated with the description "10:Ph Department" , "101:AB Dpt", "30000:Mult"

TABLECODE is as given below;

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..sales','U') IS NOT NULL

DROP TABLE sales

--===== Create the test table sales with

CREATE TABLE [dbo].[sales](

[INVOICEID] [nvarchar](255) NULL,

[NAME] [nvarchar](255) NULL,

[DIM1] [float] NULL,

[DIM2] [float] NULL,

[DIM3] [float] NULL,

[CMPY] [nvarchar](255) NULL

)

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..Dimensions','U') IS NOT NULL

DROP TABLE Dimensions

--===== Create the test table Dimensions with

CREATE TABLE [dbo].[Dimensions](

[DESCRIPTION] [nvarchar](255) NULL,

[NUM] [float] NULL,

[LEVEL] [float] NULL,

[CMPY] [nvarchar](255) NULL,

)

--===== Insert the test data into the test table sales

INSERT INTO sales

(INVOICEID, NAME, DIM1, DIM2, DIM3,CMPY)

SELECT 'SINV-0015','ORGMICRO','10','101','30000' ,'baygt' UNION ALL

SELECT 'SINV-0016','ORGMICRO','10','101','30000' ,'baygt' UNION ALL

SELECT 'SINV-0017','TRACLR','10','101','10101', 'baygt' UNION ALL

SELECT 'SINV-0018','PURECLEAN','10','101','30000', 'baygt' UNION ALL

SELECT 'SINV-0019','TRACLR','20','201','20101', 'baygt' UNION ALL

SELECT 'SINV-0020','TRACLR','30','301','30000', 'baygt'

--===== Insert the test data into the test table Dimensions

INSERT INTO Dimensions

(DESCRIPTION, NUM, LEVEL, CMPY)

SELECT 'Ph Department','10','0','baygt' UNION ALL

SELECT 'MSERVICES','30','0','baygt' UNION ALL

SELECT 'MST','20','0','baygt' UNION ALL

SELECT 'AB Dpt','101','1','baygt' UNION ALL

SELECT 'SCa','102','1','baygt' UNION ALL

SELECT 'ADM','301','1','baygt' UNION ALL

SELECT 'Act','10101','2','baygt' UNION ALL

SELECT 'Multi','30000','2','baygt' UNION ALL

SELECT 'SCaDD','201','1','baygt' UNION ALL

SELECT 'LAB','20101','2','baygt'

Abhijit More SSCertifiable Points: 6897 More actions · Answer 3

I guess this is what you are looking for....

selectdistinct NAME, cast(DIM1 as varchar(10)) + '-' + b.DESCRIPTION as DIM1,

cast(DIM2 as varchar(10)) + '-' + b.DESCRIPTION as DIM2,

cast(DIM3 as varchar(10)) + '-' + b.DESCRIPTION as DIM3, a.CMPY

from sales a

join Dimensions b on a.CMPY = b.CMPY

Abhijit - http://abhijitmore.wordpress.com

Nicky james SSC Journeyman Points: 95 More actions · Answer 4

Dear SSC, thanks for your fast reply. When i execute your query the rows repeats 3 times with each values of the 3 dimensions

INVOICEID------ITMNAME-----DIM1----------------DIM2-----------------DIM3------------------CMPY

SINV-0015------ORGMICRO----10:Ph Department---101:Ph Department---30000:Ph Department---baygt

SINV-0015------ORGMICRO----10:AB Dpt-----------101:AB Dpt----------30000:AB Dpt-----------baygt

SINV-0015------ORGMICRO----10:Multi-------------101:Multi------------30000:Multi-------------baygt

instead of the output as

SINV-0015------ORGMICRO----10:Ph Department-----101:AB Dpt---30000:Multi-----baygt

Please help

Nicky james SSC Journeyman Points: 95 More actions · Answer 5

Nicky james

SSC Journeyman

Points: 95

January 21, 2011 at 10:23 pm

#1276868

Anyone plzz help me

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 6

Nicky james (1/21/2011)
Anyone plzz help me

Gladly... you just need one join per dimension. The following code does the trick (and thanks for making it so easy with the readily consumable data you posted :-)):

SELECT s.InvoiceID,

ItmName = s.Name,

Dim1 = CAST(s.Dim1 AS VARCHAR(16))+':'+d1.Description,

Dim2 = CAST(s.Dim2 AS VARCHAR(16))+':'+d2.Description,

Dim3 = CAST(s.Dim3 AS VARCHAR(16))+':'+d3.Description,

s.Cmpy

FROM dbo.Sales s

LEFT JOIN dbo.Dimensions d1 ON s.Cmpy = d1.Cmpy AND s.Dim1 = d1.Num

LEFT JOIN dbo.Dimensions d2 ON s.Cmpy = d2.Cmpy AND s.Dim2 = d2.Num

LEFT JOIN dbo.Dimensions d3 ON s.Cmpy = d3.Cmpy AND s.Dim3 = d3.Num

ORDER BY s.InvoiceID

As a side bar, why are you using the FLOAT datatype to store (apparently) INTEGER data? It seems like a great waste of bytes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)