SQL functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL functions

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources