December 31, 2014 at 1:33 am
hi,
my data look like this.
name------ code------amount
a-------------8-----------12
b-------------8-----------12
name------ code------amount
c-------------4-----------132
d-------------5-----------124
i want data looks like this
name------ code------amount-------name-------- code------amount
a-------------8-----------12------------c-------------4-----------132
b-------------8-----------12------------d-------------5-----------124
this is my query
select c.Name,Count( Left( C.evs_Code , 3 )) Code,
Sum (S.Amount)
from evs_ccagentcodes c
Inner Join SalesOrder s On C.Code = S.EvS_VoucherCode
where
c.evs_ContactCenterAgentName like '%CCI%'
and c.evs_Code like '%ACT%'
Group By LEFT (C.Evs_Code,3) , c.Name
select c.Name,Count( Left( C.evs_Code , 3 )) Code,
Sum (S.Amount)
from evs_ccagentcodes c
Inner Join SalesOrder s On C.Code = S.EvS_VoucherCode
where
c.evs_ContactCenterAgentName like '%CCI%'
and c.evs_Code like '%CDF%'
Group By LEFT (C.Evs_Code,3) , c.Name
please help me out
thanks for the help.
immad
December 31, 2014 at 4:32 am
Please provide a sample data script. Not only will this give folks a better idea of how your data looks, it will also encourage them to participate. A dozen rows or so should be fine.
1. How do you distinguish between this block of two rows
name------ code------amount
a-------------8-----------12
b-------------8-----------12
and this block of two rows
name------ code------amount
c-------------4-----------132
d-------------5-----------124
Unless you're planning to flatten alternate rows, you will need a column to distinguish between the two blocks.
2. How do you decide which row in each block (row pair) will appear in the left three columns and which in the right three?
3. Column names should be unique in the output.
What you are asking to do could be anywhere from trivially easy to fiendishly difficult. Without the missing details and some sample data, it's pointless even showing the trivially easy method.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 31, 2014 at 10:19 am
As Chris mentioned, we can't determine how you match up rows. What determines that the row with "a" matches the row with "c" and not "d"? If you can't logically determine that somehow, you can't do this.
December 31, 2014 at 10:21 am
Also, I can repeat column names. Just need an alias.
SELECT a.myid
, a.mychar
, b.myid
FROM dbo.mytable2 a
INNER JOIN dbo.mytable b
ON b.myid = a.MYID
December 31, 2014 at 10:35 am
my guess is he wants to turn the output into multiple columns in order to save space; instead of one long three column list, he wants two columns of the same data side by side.
i've got this example, which spreads accross five columns, as an example:
declare @divisor int
select @divisor = (count(*) / 5 ) + 1 from sys.tables
;with baseCTE AS (select ROW_NUMBER() over (order by name) As RW,name from sys.tables),
firstCTE AS (select RW / @divisor as RW1, name from baseCTE),
G1 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 0),
G2 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 1),
G3 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 2),
G4 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 3),
G5 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 4)
SELECT
G1.name,
G2.name,
G3.name,
G4.name,
G5.name
FROM G1
LEFT OUTER JOIN G2 ON G1.RW = G2.RW
LEFT OUTER JOIN G3 ON G1.RW = G3.RW
LEFT OUTER JOIN G4 ON G1.RW = G4.RW
LEFT OUTER JOIN G5 ON G1.RW = G5.RW
--the correct example:
WITH
baseCTE AS
(
SELECT TOP(24*5)
(ROW_NUMBER() OVER (ORDER BY Name)-1)/5+1 AS RW,
(ROW_NUMBER() OVER (ORDER BY Name)-1)%5+1 AS CL,
Name
FROM sys.tables
)
SELECT MAX(CASE WHEN CL = 1 THEN Name ELSE '' END) AS Col1TableName,
MAX(CASE WHEN CL = 2 THEN Name ELSE '' END) AS Col2TableName,
MAX(CASE WHEN CL = 3 THEN Name ELSE '' END) AS Col3TableName,
MAX(CASE WHEN CL = 4 THEN Name ELSE '' END) AS Col4TableName,
MAX(CASE WHEN CL = 5 THEN Name ELSE '' END) AS Col5TableName
FROM baseCTE
GROUP BY RW
;
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply