Multiple Select statements for insert into table

Question

Post reply

Multiple Select statements for insert into table

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
May 3, 2012 at 1:09 pm

#255833

Hi I have a table like as below :
Date StoreName ItemID Lcount
11/22/2011 AAAAA Z123 5
11/22/2011 BBBBBB Y688 6
11/22/2011 CCCCC N145 5
11/23/2011 DDDDD M148 9
11/23/2011 BBBBBB B636 10
11/24/2011 CCCCC X157 2
11/25/2011 DDDDD Y123 4
With the above Table Data I need to create a new table with columns as DateStoreName Lcount1Lcount2Lcount3
LCount1= SUM(Lcount) Where ItemId in (Z123,M148)
Lcount2=SUM(Lcount) Where ItemId in (N145,Y688)
Lcount3=SUM(Lcount) Where ItemId in (B636,X157)
LCount4=SUM(Lcount) Where ItemId in (Y123,B636)
Thanks in Advance for your suggestions ...

Viewing 4 posts - 1 through 3 (of 3 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Totally lost.... can you be more elaborate on what u r trying to accomplish?

take a look at this article for more information -> How to post questions[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

It would help to have your DDL and sample data in consumable form. I only put in the ItemID and Lcount for my sample. Here's a simple attempt:

CREATE TABLE #MyTable (ItemID varchar(20), Lcount int)

INSERT INTO #MyTable

SELECT 'Z123', 5 UNION ALL

SELECT 'Y688', 5 UNION ALL

SELECT 'N145', 5 UNION ALL

SELECT 'M148', 9 UNION ALL

SELECT 'B636', 10 UNION ALL

SELECT 'X157',2 UNION ALL

SELECT 'Y123', 4

SELECT * FROM #MyTable

WITH SummaryCTE AS (

SELECT ItemID, SUM(Lcount) AS Lcount

FROM #MyTable

GROUP BY ItemID

)

SELECT 'Lcount1 (Z123,M148)', SUM(Lcount)

FROM SummaryCTE

WHERE ItemID IN ('Z123','M148')

UNION ALL

SELECT 'Lcount2 (N145,Y688)', SUM(Lcount)

FROM SummaryCTE

WHERE ItemID IN ('N145','Y688')

UNION ALL

SELECT 'Lcount3 (B636,X157)', SUM(Lcount)

FROM SummaryCTE

WHERE ItemID IN ('B636','X157')

UNION ALL

SELECT 'Lcount4 (Y123,B636)', SUM(Lcount)

FROM SummaryCTE

WHERE ItemID IN ('Y123','B636')

Converting oxygen into carbon dioxide, since 1955.

vinu512 SSCoach Points: 15729 More actions · Answer 3

This might help:

--Creating Table

Create Table Ex

(Date Date,

StoreName varchar(10),

ItemID varchar(10),

Lcount int )

--Inserting Sample Data

Insert Into Ex

Select '11/22/2011', 'AAAAA', 'Z123', 5

Union ALL

Select '11/22/2011', 'BBBBBB', 'Y688', 6

Union ALL

Select '11/22/2011', 'CCCCC', 'N145', 5

Union ALL

Select '11/23/2011', 'DDDDD', 'M148', 9

Union ALL

Select '11/23/2011', 'BBBBBB', 'B636', 10

Union ALL

Select '11/24/2011', 'CCCCC', 'X157', 2

Union ALL

Select '11/25/2011', 'DDDDD', 'Y123', 4

--Query for your requirement

Select

Sum(Case When ItemId in ('Z123','M148') then Lcount else 0 end) As LCount1,

Sum(Case When ItemId in ('N145','Y688') then Lcount else 0 end) As LCount2,

Sum(Case When ItemId in ('B636','X157') then Lcount else 0 end) As LCount3,

Sum(Case When ItemId in ('Y123','B636') then Lcount else 0 end) As LCount4

From Ex

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉