Dynamic Crosstab Query

if you could provide us a more detailed description of the columns and formula, i think it would be easier to assist you.

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature. You might want to also look at the links in my signature for performing Cross-Tab queries.

Waynes,

Thank you very much for your reply and also I am sorry for my mistake.

here is my table structure:

create table mytest

(

smodel varchar(50),

lfisno varchar(50),

nstoktipi int,

sfistipi char(50),

lbrutfiyat float,

lIlaveMaliyetTutar float

)

I have inserted some data as follows:

INSERT INTO mytest values('XXXXXXXX','9999999',2,'FA',1,166.67)

GO

UNION ALL

INSERT INTO mytest values('159 010 100','9999999',4,'HA',100,0)

GO

UNION ALL

INSERT INTO mytest values('159 010 106','9999999',4,'HA',500,0)

GO

UNION ALL

INSERT INTO mytest values('159 010 105','9999999',4,'HA',100,0)

GO

UNION ALL

INSERT INTO mytest values('YYYYYYYY','9999999',2,'FA',5,833.33)

GO

My desired resultset will consist just the real stock codes and their cost distribution such as:

smodel 159 010 100 159 010 106 159 010 105

---------------- ---------------- -------------- ----------------------

XXXXXXXXX 16.667 83.335 66.668

YYYYYYYY 83.333 416.665 333.332

The HA row values are transformed to my column values.

There are 2 steps of my issue:

1) How can I write this crosstab query?

2) These HA values are dynamic. How can I apply a dynamic crosstab functionality?

King Regards

Sorry,

my insert statements like below:

INSERT INTO mytest values('XXXXXXXX','9999999',2,'FA',1,166.67)

GO

UNION ALL

INSERT INTO mytest values('159 010 100','9999999',4,'HA',100,0)

GO

UNION ALL

INSERT INTO mytest values('159 010 106','9999999',4,'HA',500,0)

GO

UNION ALL

INSERT INTO mytest values('159 010 105','9999999',4,'HA',400,0)

GO

UNION ALL

INSERT INTO mytest values('YYYYYYYY','9999999',2,'FA',5,833.33)

GO

Would you mind to explain how you came up with the values maked bold and underlined in the quote below? I can't find the values nor any kind of formula...

I will try to explain it for the stock code XXXXXX row:

SUM of HA values is 1000. (100+500+400)

'159 010 100' ---> 166.67 x (100/1000) = 16.667

'159 010 106'----> 166.68 x (500/1000) = 83.335

'159 010 105'-----> 166.68 x (400/1000) = 66.668

I think I have found something, and my resultset is as follows:

lfisno smodel X1 X2 X3

999999999 XXXXXXXX 16.566998 66.267992 82.834990

999999999 YYYYYYYY 82.833002 331.332008 414.165010

This is great. But how can I apply b.smodel='159 010 100' statement dynamic?

I mean this should be a dynamic crosstab query.

select a.lfisno,a.smodel,

SUM(CASE when b.sfistipi='HA' and b.smodel='159 010 100' then b.lbrutfiyat else 0 end)/SUM(b.lbrutFiyat) * a.lIlaveMaliyetTutar as X1,

SUM(CASE when b.sfistipi='HA' and b.smodel='159 010 105' then b.lbrutfiyat else 0 end)/SUM(b.lbrutfiyat) * a.lIlaveMaliyetTutar as X2,

SUM(CASE when b.sfistipi='HA' and b.smodel='159 010 106' then b.lbrutfiyat else 0 end)/SUM(b.lbrutfiyat) * a.lIlaveMaliyetTutar as X3

--SUM(b.lbrutfiyat) as Total

from erdem_fistipleri2 a

INNER JOIN erdem_fistipleri2 b

ON a.lfisno=b.lfisno

where a.nstoktipi=2

group by a.lfisno,a.smodel,a.lIlaveMaliyetTutar

For learning how to build dynamic cross tabs please see the related link in my signature.

When applied to your scenario it could look like the following code:

-- step 1: build a intermediate table to get the data in a more relational format

SELECT m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/1000 AS RESULT

INTO #subresults

FROM mytest m

CROSS APPLY

(

SELECT smodel,lbrutfiyat

FROM mytest c

WHERE sfistipi='HA'

)c

WHERE m.sfistipi='FA'

SELECT *

FROM #subresults

-- build the dynamic cross tab query

DECLARE

@SQL1 NVARCHAR(4000),

@SQL2 NVARCHAR(4000),

@SQL3 NVARCHAR(4000)

SELECT @SQL1 = 'SELECT smodel'+CHAR(10)

--===== Create the "Mostly Static" section of the code

SELECT @SQL3 = ' FROM #subresults group by smodel'

--===== The "Hard" Part - Concatenation to Form the Date Columns

SELECT @SQL2 = COALESCE(@SQL2,'')

+ ' ,SUM(CASE WHEN col = ' + QUOTENAME(s.col,'''')

+ ' THEN result ELSE 0 END) AS [' + s.col + ']' + CHAR(10)

FROM #subresults s

GROUP BY col

--===== Print the Dynamic SQL (uncomment for troubleshooting

-- PRINT @SQL1 + @SQL2 + @SQL3

--===== Execute the Dynamic SQL to create the desired report

EXEC (@SQL1 + @SQL2 + @SQL3)

Oh..What can I say.It is really a crazy perfect solution.

SELECT m.smodel,c.smodel AS col,m.lIlaveMaliyetTutar * c.lbrutfiyat/1000 AS RESULT

INTO #subresults

But the value 1000 is the sum of the lbrutfiyat values. How can I replace this value as SUM(lbrutfiyat)?

You could simply add the sum of lbrutfiyat to the CROSS APPLY part.

The interesting part is the SUM() OVER(PARTITION BY) clause, which is not seen that often. But it helps to avoid another CROSS APPLY or subquery....

SELECT

m.smodel,

c.smodel AS col,

m.lIlaveMaliyetTutar * c.lbrutfiyat / c.total AS RESULT

INTO #subresults

FROM mytest m

CROSS APPLY

(

SELECT

smodel,

lbrutfiyat,

SUM(lbrutfiyat) OVER(PARTITION BY sfistipi) as total

FROM mytest c

WHERE sfistipi='HA'

)c

WHERE m.sfistipi='FA'

I have modified your code like below.Because the main idea here is to get individual sums of lfisno columns.

I have also added the lfisno column to the dynamic SQL section

Unfortunately I could not get the correct result.

Any idea?

SELECT distinct(lfisno),

m.smodel,

c.smodel AS col,

m.lIlaveMaliyetTutar * c.lbrutfiyat / c.total AS RESULT

INTO #subresults

FROM erdem_fistipleri3 m

CROSS APPLY

(

SELECT

smodel,

lbrutfiyat,

SUM(lbrutfiyat) OVER(PARTITION BY sfistipi) as total

FROM erdem_fistipleri3 c

WHERE sfistipi='HA'

)c

WHERE m.sfistipi='FA'

I have no idea what you mean by "individual sums"... :ermm:

Would you mind providing some more sample data together with your expected output?

Remember: we can't look over your shoulder so we have to use what you provided. And based on that: it seemed to work...

Thank you very much for your great helps. I will try to solve other issues. And believe that it was a great vision to learn something.

Regards

My pleaseure. 🙂

If you get stuck, post some sample data to describe the scenario together with your expected result and we'll try to help again.