| Author |
Topic |
|
baek_as
Starting Member
1 Post |
 Posted - 2011-03-25 : 06:18:44
|
Hello
I have got a question from one of my colleagues.
In Access we are having this funktion
TRANSFORM Sum(PMONTH_PPERS_Projects.Sum_mdr) AS [The Value]
SELECT [PPERS Query].DEPARTMENT, [PPERS Query].PERS_NO, [PPERS Query].SHORT_NAME, PMONTH_PPERS_Projects.MONTH, Startsaldo.Expr1, Slutsaldo.Expr1, Sum(PMONTH_PPERS_Projects.Sum_mdr) AS [Total Of Sum_mdr]
FROM Slutsaldo RIGHT JOIN (Startsaldo RIGHT JOIN ([PPERS Query] LEFT JOIN PMONTH_PPERS_Projects ON [PPERS Query].PERS_NO = PMONTH_PPERS_Projects.PERS_NO) ON Startsaldo.PERS_NO = [PPERS Query].PERS_NO) ON Slutsaldo.PERS_NO = [PPERS Query].PERS_NO
GROUP BY [PPERS Query].DEPARTMENT, [PPERS Query].PERS_NO, [PPERS Query].SHORT_NAME, PMONTH_PPERS_Projects.MONTH, Startsaldo.Expr1, Slutsaldo.Expr1
ORDER BY [PPERS Query].DEPARTMENT, [PPERS Query].SHORT_NAME
PIVOT PMONTH_PPERS_Projects.PROJECT In ("001","193","196","201","207","213","215","217","219","220","224","226","230","231","236","241","242","245","246","247","248","249","250","251","252","253","254","255","256","257","258","259","260","261","262","998","SPE");
My question is then : what is the max length of the string in the PIVOT funtion ? and how can I extend it ??
Best regards
Asbjørn |
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
Posted - 2011-03-25 : 07:50:30
|
Ok you are increasing dynamically column I think
DECLARE @colm Nvarchar(max)
SELECT @colm=COALESCE (@colm+ ',['+column_name+']')
from <your join query >
select @colm
may be this what you are looking for
How it works
create table #A(id int, product nvarchar(25),valuess int)
insert into #A values ('1','HI','125')
insert into #A values ('2','Hello','126')
DECLARE @colm Nvarchar(max)
SELECT @colm=COALESCE (@colm+ ',['+product+']','['+product+']')
from #A
select @colm
--Next_Example
DECLARE @colm Nvarchar(max)
SELECT @colm=COALESCE (@colm+ ''+ product+'',''+product +'')
from #A
select @colm
--Two table with join
create table #B (id int, sales nvarchar(25),valuess int)
insert into #B values('1','Think','521')
insert into #B values('2','HowThink','522')
DECLARE @colm Nvarchar(max)
SELECT @colm=COALESCE (@colm+ ''+ product+''+sales+'',''+product+''+sales+'')
from #A,#B
select @colm
DECLARE @colm Nvarchar(max)
SELECT @colm=COALESCE (@colm+ ''+ product+''+sales+'',''+product+''+sales+'')
from #A,#B where #A.id=#B.id
select @colm
drop table #A
drop table #B
Raghu' S |
 |
|
|
|
|
|
SQL functions