August 11, 2016 at 8:21 am
Hi Friends ,
i would like to replace null as zero , please some one help me . for ur reference i have paste code and my pivot query below.
-- Table :
create table #2_2_2 (cc_key varchar(25), modeltrim_oemcode varchar (500), standard_equipment_name_natlang varchar (500),
modeltrim_name varchar (500),category_name_natlang varchar(500))
insert into #2_2_2 values ('A1','Code1','Innovation pack','BASE',NULL)
insert into #2_2_2 values ('A1','Code1','Innovation pack','ADVANTAGE',NULL)
insert into #2_2_2 values ('A1','Code1','Innovation pack','SPORT',NULL)
insert into #2_2_2 values ('A1','Code2','Travel Pack','SPORT',NULL)
insert into #2_2_2 values ('A1','Code2','Travel Pack','M SPORT','Equipamiento de seguridad, funcional y de confort')
insert into #2_2_2 values ('A1','Code2','Travel Pack','LUXURY','Equipamiento de seguridad, funcional y de confort')
insert into #2_2_2 values ('A1','Code3','Magic pack','BASE',null)
insert into #2_2_2 values ('A1','Code3','Magic pack','SPORT',NULL)
insert into #2_2_2 values ('A1','Code3','Magic pack','M SPORT','Equipamiento de seguridad, funcional y de confort')
insert into #2_2_2 values ('A1','Code3','Magic pack','LUXURY','super')
-- select * from #2_2_2
-- dynamic pivot Query :
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.modeltrim_name)
FROM #2_2_2 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT modeltrim_oemcode,category_name_natlang,standard_equipment_name_natlang, ' + @cols + ' from
(
select modeltrim_oemcode,category_name_natlang , standard_equipment_name_natlang , modeltrim_name , ''S'' as MNT
from #2_2_2
) x
pivot
(
max(MNT)
for modeltrim_name in (' + @cols + ')
) p '
execute(@query);
August 11, 2016 at 9:03 am
Excellent job posting ddl and sample data!!! I wish everyone would put that much effort into their posts.
You can accomplish this fairly easily using replace.
set @query = 'SELECT modeltrim_oemcode,category_name_natlang,standard_equipment_name_natlang, ' + replace(replace(@cols, '[', 'ISNULL(['), ']', '], 0)') + ' from
(
select modeltrim_oemcode,category_name_natlang , standard_equipment_name_natlang , modeltrim_name , ''S'' as MNT
from #2_2_2
) x
pivot
(
max(MNT)
for modeltrim_name in (' + @cols + ')
) p '
FWIW, I prefer to use a dynamic cross tab instead of a dynamic pivot. I find the syntax less obtuse and it is slightly faster (but very little). I probably wouldn't convert this since you already have it completed. But you can read more about dynamic pivots by following the link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2016 at 9:32 am
Dear Sean Lange,
Your Query is working awesome . but column names are showing like no column .. but coumn name are very must to my process.
i was tried to get using alise . but it was comming to wrongly like concatination format.
please kindly help me.
August 11, 2016 at 10:08 am
Anandkumar-SQL_Developer (8/11/2016)
Dear Sean Lange,Your Query is working awesome . but column names are showing like no column .. but coumn name are very must to my process.
i was tried to get using alise . but it was comming to wrongly like concatination format.
please kindly help me.
Another easy solution. I was hoping you would figure this one out. You can simply create another variables for your ColumnValues.
DECLARE @cols AS NVARCHAR(MAX),
@ColValues as nvarchar(max),
@query AS NVARCHAR(MAX);
Then you can use the same technique you used to populate the dynamic list of columns but adding the ISNULL and column alias.
SET @ColValues = STUFF((SELECT distinct ',ISNULL(' + QUOTENAME(c.modeltrim_name) + ', 0) as ' + QUOTENAME(c.modeltrim_name)
FROM #2_2_2 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
Last but not least you would use this in your select statement.
set @query = 'SELECT modeltrim_oemcode,category_name_natlang,standard_equipment_name_natlang, ' + @ColValues + ' from
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2016 at 10:40 pm
Here's the Dynamic CROSSTAB version. I believe you might find it a little easier to read, modify, and troubleshoot.
--===== Local Variables
DECLARE @SQL NVARCHAR(MAX)
,@DynamicList NVARCHAR(MAX) = ''
;
--===== Create the dynamic columns for the SELECT list
SELECT @DynamicList += REPLACE(REPLACE(
N',[<<modeltrim_name>>] = MAX(CASE WHEN modeltrim_name = "<<modeltrim_name>>" THEN "S" ELSE "" END)
' ,N'"',N'''')
,N'<<modeltrim_name>>',modeltrim_name)
FROM #2_2_2
GROUP BY modeltrim_name
ORDER BY modeltrim_name
;
--===== Wrap the dynamic part of the SELECT list in the rest of the code.
SELECT @SQL = REPLACE(N'
SELECT modeltrim_oemcode
,category_name_natlang
,standard_equipment_name_natlang
<<@DynamicList>>
FROM #2_2_2
GROUP BY modeltrim_oemcode
,category_name_natlang
,standard_equipment_name_natlang
;' ,N'<<@DynamicList>>',@DynamicList)
;
--===== Display and execute the Dynamic SQL
PRINT @SQL;
EXEC (@SQL);
The method for making such Dynamic CROSSTABs is explained at the following URL.
http://www.sqlservercentral.com/articles/Crosstab/65048/
As for CROSSTABs being a "little" faster than PIVOTs... "It Depends". With pre-aggregation (thank you Peter Larsson), they can be twice as fast. See the following article on that.
http://www.sqlservercentral.com/articles/T-SQL/63681/
"Give a man a fish, and he'll eat for a day. Teach a man to fish, and he'll be able to buy a boat and drink some beer while catching lots of fish.". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 11, 2016 at 10:49 pm
p.s. NEVER use that type of Dynamic SQL in anything that's public facing, especially where modeltrim_name (in this case) could be entered by users, because it IS susceptible to SQL Injection. That goes for the PIVOT version, as well.
If it MUST be public facing, delouse the modeltrim_name column first.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2016 at 12:05 am
Dear SSC-Forever ,
Really u r genius. u have done awsome.., even without using pivot operator u transpose all row perfectly. really i feel wonder about u. Thank u very much for ur valuable time. have a nice day.
August 12, 2016 at 7:33 am
Jeff Moden (8/11/2016)
Here's the Dynamic CROSSTAB version. I believe you might find it a little easier to read, modify, and troubleshoot.
Thanks Jeff. I was going to roll this into the crosstab version today. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2016 at 8:18 am
Anandkumar-SQL_Developer (8/11/2016)
Hi Friends ,i would like to replace null as zero , please some one help me . for ur reference i have paste code and my pivot query below.
First thing, please repeat the question in the actual post, it can be hard to get the context when responding on a mobile device 😉
Secondly, a question, are you doing this because you have columns which have only NULL values? The reason for asking is that otherwise this exercise does not make sense, max will always eliminate nulls if any values are present. The easiest workaround would be creating a view which replaces nulls and cross-tap/pivot from there.
😎
August 12, 2016 at 4:21 pm
Anandkumar-SQL_Developer (8/12/2016)
Dear SSC-Forever ,Really u r genius. u have done awsome.., even withput using pivot operator u transpose all row perfectly. really i feel wonder about u. Thank u very much for ur valuable time. have a nice day.
Thank you for the kind feedback. Very glad to help.
Also, "SSC-Forever" is a "rank" on this forum... not my name. My name is above the moving Avatar (Jeff Moden).
Shifting gears... thank you but I'm not so much a genius... I'm a bit lazy and PIVOT takes too much thought for me to do even simple things with it. 😉 The method I used (CROSSTABs) is actually older than a lot of Developers on this site. I'll never understand why they removed it from Books Online. If you'd like to learn more about it, please read the two articles I provided links to. And, to reiterate, there are times where you can make it run twice as fast as a PIVOT using "pre-aggregation". That's explained in the "Part 1" article, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply