How to select in this case - very complex SQL

Question

How to select in this case - very complex SQL

nguyennd

SSCommitted

Points: 1881
More actions
March 12, 2010 at 4:13 am

#217811

I have a table
CREATE TABLE AAATable
(
K INT,
SKU VARCHAR(1),
KBN INT,
MONEY INT
)
with data
INSERT INTO AAATable(K,A,B,MONEY)
SELECT 1 as K, 'A' as SKU, 1 as KBN, 1000 as MONEY
UNION ALL SELECT 2 as K, 'B' as A, 1 as B, 2000 as MONEY
UNION ALL SELECT 3 as K, 'C' as A, 3 as B, 300 as MONEY
UNION ALL SELECT 4 as K, 'D' as A, 4 as B, 400 as MONEY
i want to get a result
KSKUKBNMONEYMONEY34
1A11000700 <= sum if KBN =3,4
2B12000700 <= sum if KBN =3,4
Please help me
http://unitech.vn

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Not sure I entirely understand your requirement. Are you using <= as "less than or equal to" or are you simply pointing to the columns to explain the logic?

The following fits the expected output, but I'm not sure it's what you require without further clarification

SELECT K, SKU, KBN,MONEY, ISNULL(MONEY34.TOTAL,0) AS MONEY34

FROM AAATable

CROSS JOIN (SELECT SUM(MONEY) AS TOTAL FROM #aaatest WHERE KBN IN (3,4)) MONEY34

WHERE KBN IN (1,2)

Cheers,

Howard

nguyennd SSCommitted Points: 1881 More actions · Answer 2

Thanks HowardW , a good asnwer

😀

http://unitech.vn

nguyennd SSCommitted Points: 1881 More actions · Answer 3

hic, i have another question

My table

CREATE TABLE [dbo].[AAATable01](

[K1] [int] NOT NULL,

[K2] [int] NOT NULL,

[KBN] [int] NULL,

[SKU] [int] NULL,

[MONEY] [int] NULL,

)

With data

K1 K2 KBN SKU MONEY

1 1 1 11 1

1 2 1 22 2

1 3 3 33 3

1 4 4 44 4

2 1 1 55 5

2 2 2 66 6

2 3 3 77 9

2 4 1 88 8

I need sql to get data

K1 K2 KBN SKU MONEY MONEY34

1 1 1 11 1 7

1 2 1 22 2 7

2 1 1 55 5 9

2 2 2 66 6 9

2 4 1 88 8 9

In this case, i've use cursor but i don't like cursor 😀

Can you help me?

http://unitech.vn

Paul White SSC Guru Points: 150467 More actions · Answer 4

CREATE TABLE dbo.AAATable01

(

K1 INTEGER NOT NULL,

K2 INTEGER NOT NULL,

KBN INTEGER NULL,

SKU INTEGER NULL,

[MONEY] INTEGER NULL,

);

GO

INSERT dbo.AAATable01

(K1, K2, KBN, SKU, [MONEY])

VALUES (1, 1, 1, 11, 1),

(1, 2, 1, 22, 2),

(1, 3, 3, 33, 3),

(1, 4, 4, 44, 4),

(2, 1, 1, 55, 5),

(2, 2, 2, 66, 6),

(2, 3, 3, 77, 9),

(2, 4, 1, 88, 8);

GO

-- Solution 1

SELECT T1.K1,

T1.K2,

T1.KBN,

T1.SKU,

CA.MONEY34

FROM dbo.AAATable01 T1

OUTER

APPLY (

SELECT MONEY34 = SUM(MONEY)

FROM dbo.AAATable01 T2

WHERE T2.KBN IN (3,4)

AND T2.K1 = T1.K1

) CA

WHERE T1.KBN NOT IN (3,4);

-- Solution 2

SELECT T1.K1,

T1.K2,

T1.KBN,

T1.SKU,

MONEY34 =

(

SELECT MONEY34 = SUM(MONEY)

FROM dbo.AAATable01 T2

WHERE T2.KBN IN (3,4)

AND T2.K1 = T1.K1

)

FROM dbo.AAATable01 T1

WHERE T1.KBN NOT IN (3,4);

GO

DROP TABLE dbo.AAATable01;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 5

Please read the article at the first link in my signature below. You'll get better help. A description of what you want to actually accomplish would also be a help.

--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)