January 21, 2022 at 12:56 pm
hello everyone,
i have a query with respect to pivot. As per my understanding pivot is used to transpose rows to columns. however as i was going through the documents and examples, the pivot works with a aggregate value. what if i don't want to do any aggregate ? what if there's any varchar value to be processed/represented as pivot ?
please help.
(apologies if its a stupid question)
January 21, 2022 at 2:44 pm
For example? maybe check out Jeff Moden's article on Crosstabs
January 21, 2022 at 5:53 pm
Can you use a MIN or MAX on one of the varchar columns?
I also recommend reading Jeff's two articles on Pivots and Cross Tabs.
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 24, 2022 at 12:54 pm
I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:
businessday category itemno value
2022-01-01 xyz banana 12.0 / $2.5
2022-01-01 xyz apple 10.0 / $4
2022-01-01 xyz fish 10.0
2022-01-01 abc spinach 1.0 / $0.25
2022-01-01 abc chicken 5.0
End result should be like the following. Report will be shown on rolling over basis for last 7 days. Only thing is that the value is of varchar type because of some special criteria. It contains data in two formats (numbers and numbers / $(value) formats). This is giving me headache. Need to do it dynamically as the item no will vary:
businessday category banana apple fish spinach chicken
2022-01-01 xyz 12.0 / $2.5 10.0 / $4 10.0 0.0 0.0
2022-01-01 def 0.0 0.0 0.0 1.0 / $0.25 5.0
January 24, 2022 at 8:04 pm
I think this will help ( but maybe not?! 🙂 ). What I sometimes do is rather than using fully dynamic SQL, I use placeholder column names and then rename them prior to output (or in a final SELECT). Sometimes static SQL is easier to deal with (and especially debug).
/**** set up sample data ******************************************************/DROP TABLE IF EXISTS #data;
CREATE TABLE #data (
businessday date NOT NULL,
category varchar(30) NULL,
itemno varchar(50) NULL,
value varchar(5000) NULL
)
INSERT INTO #data VALUES
('2022-01-21', 'xyz', 'banana', '12.0 / $2.5'),
('2022-01-21', 'xyz', 'apple', '10.0 / $4'),
('2022-01-21', 'xyz', 'fish', '10.0'),
('2022-01-21', 'abc', 'spinach', '1.0 / $0.25'),
('2022-01-21', 'abc', 'chicken', '5.0')
--SELECT * FROM #data
/**** code to produce wanted report *******************************************/DECLARE @item_counter int
DECLARE @itemno varchar(50)
DECLARE @sql nvarchar(max)
DECLARE @start_date date
SET @start_date = DATEADD(DAY, -7, GETDATE())
DROP TABLE IF EXISTS #itemnos;
--pre-create the table to:
--(1) avoid keeping system tables locks while the table is loading
--(2) allow a clustered index to be built *prior* to loading the table
SELECT DISTINCT TOP (0)
itemno, ROW_NUMBER() OVER(ORDER BY itemno) AS item_counter
INTO #itemnos
FROM #data
WHERE businessday >= @start_date
--
CREATE UNIQUE CLUSTERED INDEX itemnos__CL
ON #itemnos ( itemno )
WITH ( FILLFACTOR = 100 );
--
INSERT INTO #itemnos
SELECT DISTINCT
itemno, ROW_NUMBER() OVER(ORDER BY itemno) AS item_counter
FROM #data
WHERE businessday >= @start_date
DROP TABLE IF EXISTS #results;
--pre-create the table to:
--(1) avoid keeping system tables locks while the table is loading
--(2) allow a clustered index to be built *prior* to loading the table
SELECT DISTINCT TOP (0)
businessday, category,
MAX(CASE WHEN i.item_counter = 1 THEN value END) AS value1,
MAX(CASE WHEN i.item_counter = 2 THEN value END) AS value2,
MAX(CASE WHEN i.item_counter = 3 THEN value END) AS value3,
MAX(CASE WHEN i.item_counter = 4 THEN value END) AS value4,
MAX(CASE WHEN i.item_counter = 5 THEN value END) AS value5,
MAX(CASE WHEN i.item_counter = 6 THEN value END) AS value6,
MAX(CASE WHEN i.item_counter = 7 THEN value END) AS value7,
MAX(CASE WHEN i.item_counter = 8 THEN value END) AS value8,
MAX(CASE WHEN i.item_counter = 9 THEN value END) AS value9
INTO #results
FROM #data d
INNER JOIN #itemnos i ON i.itemno = d.itemno
WHERE businessday >= @start_date
GROUP BY businessday, category
--
CREATE UNIQUE CLUSTERED INDEX data__CL
ON #results ( businessday, category )
WITH ( FILLFACTOR = 100 );
--
INSERT INTO #results
SELECT
businessday, category,
MAX(CASE WHEN i.item_counter = 1 THEN value END) AS value1,
MAX(CASE WHEN i.item_counter = 2 THEN value END) AS value2,
MAX(CASE WHEN i.item_counter = 3 THEN value END) AS value3,
MAX(CASE WHEN i.item_counter = 4 THEN value END) AS value4,
MAX(CASE WHEN i.item_counter = 5 THEN value END) AS value5,
MAX(CASE WHEN i.item_counter = 6 THEN value END) AS value6,
MAX(CASE WHEN i.item_counter = 7 THEN value END) AS value7,
MAX(CASE WHEN i.item_counter = 8 THEN value END) AS value8,
MAX(CASE WHEN i.item_counter = 9 THEN value END) AS value9
FROM #data d
INNER JOIN #itemnos i ON i.itemno = d.itemno
WHERE businessday >= @start_date
GROUP BY businessday, category
ORDER BY businessday, category
DECLARE rename_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT itemno, item_counter
FROM #itemnos
ORDER BY item_counter
OPEN rename_cursor;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM rename_cursor INTO @itemno, @item_counter
IF @@FETCH_STATUS <> 0
BREAK;
SET @sql = 'EXEC tempdb.sys.sp_rename ''#results.value' +
CAST(@item_counter AS varchar(3)) + ''', ''' + @itemno + ''', ''COLUMN'''
EXEC(@sql)
END /*WHILE*/CLOSE rename_cursor;
DEALLOCATE rename_cursor;
SELECT *
FROM #results
ORDER BY businessday, category
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 24, 2022 at 11:38 pm
I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:
businessday category itemno value
2022-01-01 xyz banana 12.0 / $2.5
2022-01-01 xyz apple 10.0 / $4
2022-01-01 xyz fish 10.0
2022-01-01 abc spinach 1.0 / $0.25
2022-01-01 abc chicken 5.0End result should be like the following. Report will be shown on rolling over basis for last 7 days. Only thing is that the value is of varchar type because of some special criteria. It contains data in two formats (numbers and numbers / $(value) formats). This is giving me headache. Need to do it dynamically as the item no will vary:
businessday category banana apple fish spinach chicken
2022-01-01 xyz 12.0 / $2.5 10.0 / $4 10.0 0.0 0.0
2022-01-01 def 0.0 0.0 0.0 1.0 / $0.25 5.0
Are the itemno-based columns in the end result in any particular order?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 25, 2022 at 1:38 pm
sqlenthu 89358 wrote:I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:
businessday category itemno value
2022-01-01 xyz banana 12.0 / $2.5
2022-01-01 xyz apple 10.0 / $4
2022-01-01 xyz fish 10.0
2022-01-01 abc spinach 1.0 / $0.25
2022-01-01 abc chicken 5.0End result should be like the following. Report will be shown on rolling over basis for last 7 days. Only thing is that the value is of varchar type because of some special criteria. It contains data in two formats (numbers and numbers / $(value) formats). This is giving me headache. Need to do it dynamically as the item no will vary:
businessday category banana apple fish spinach chicken
2022-01-01 xyz 12.0 / $2.5 10.0 / $4 10.0 0.0 0.0
2022-01-01 def 0.0 0.0 0.0 1.0 / $0.25 5.0Are the itemno-based columns in the end result in any particular order?
in naming order:
order by itemno ASC
January 25, 2022 at 1:47 pm
I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:
businessday category itemno value
2022-01-01 xyz banana 12.0 / $2.5
2022-01-01 xyz apple 10.0 / $4
2022-01-01 xyz fish 10.0
2022-01-01 abc spinach 1.0 / $0.25
2022-01-01 abc chicken 5.0End result should be like the following. Report will be shown on rolling over basis for last 7 days. Only thing is that the value is of varchar type because of some special criteria. It contains data in two formats (numbers and numbers / $(value) formats). This is giving me headache. Need to do it dynamically as the item no will vary:
businessday category banana apple fish spinach chicken
2022-01-01 xyz 12.0 / $2.5 10.0 / $4 10.0 0.0 0.0
2022-01-01 def 0.0 0.0 0.0 1.0 / $0.25 5.0
If you actually want coded help, then you need to look at one more article and that would be the first article in my signature line below. You've been around long enough to know this. Do it now. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2022 at 3:31 pm
Obviously there's no need for nullable columns or a cursor
drop table if exists #data;
go
create table #data (
businessday date not null,
category varchar(30) not null,
itemno varchar(50) not null,
[value] varchar(5000) not null);
insert into #data(businessday, category, itemno, [value]) values
('2022-01-21', 'xyz', 'banana', '12.0 / $2.5'),
('2022-01-21', 'xyz', 'apple', '10.0 / $4'),
('2022-01-21', 'xyz', 'fish', '10.0'),
('2022-01-21', 'def', 'spinach', '1.0 / $0.25'),
('2022-01-21', 'def', 'chicken', '5.0');
--select * from #data
declare
@sql_prefix nvarchar(max)=N'select businessday, category, ',
@sql_suffix nvarchar(max)=N' from #data group by businessday, category order by businessday, category desc;',
@pvt_prefix nvarchar(max)=N'max(case when itemno=''',
@pvt_middle nvarchar(max)=N''' then [value] else ''0'' end) [',
@pvt_suffix nvarchar(max)=N']',
@sql nvarchar(max);
with
unq_itemno_cte(itemno) as (
select distinct itemno
from #data),
pvt_cte(itemno, string) as (
select itemno, concat(@pvt_prefix,
itemno,
@pvt_middle,
itemno,
@pvt_suffix)
from unq_itemno_cte)
select @sql=concat(@sql_prefix,
string_agg(string, N',') within group (order by itemno),
@sql_suffix)
from pvt_cte;
print(@sql);
/* generates the following code (when formatted) *//*
select businessday, category,
max(case when itemno='apple' then [value] else '0' end) [apple],
max(case when itemno='banana' then [value] else '0' end) [banana],
max(case when itemno='chicken' then [value] else '0' end) [chicken],
max(case when itemno='fish' then [value] else '0' end) [fish],
max(case when itemno='spinach' then [value] else '0' end) [spinach]
from #data
group by businessday, category
order by businessday, category desc;
*/
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 25, 2022 at 3:47 pm
STRING_AGG is not available in SQL Server 2016.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 25, 2022 at 5:01 pm
STRING_AGG is not available in SQL Server 2016.
Maybe try starting here for examples of the old way
One modern element I would add would be to use CONCAT_WS ("concat with separator") instead of CONCAT in the outer SELECT. That way the trailing space in @pvt_prefix and the leading space in @sql_suffix could be replaced with a single space as the separator
declare
@sql_prefix nvarchar(max)=N'select businessday, category,',
@sql_suffix nvarchar(max)=N'from #data group by businessday, category order by businessday, category desc;',
@pvt_prefix nvarchar(max)=N'max(case when itemno=''',
@pvt_middle nvarchar(max)=N''' then [value] else ''0'' end) [',
@pvt_suffix nvarchar(max)=N']',
@sql nvarchar(max);
with
unq_itemno_cte(itemno) as (
select distinct itemno
from #data),
pvt_cte(itemno, string) as (
select itemno, concat(@pvt_prefix,
itemno,
@pvt_middle,
itemno,
@pvt_suffix)
from unq_itemno_cte)
select @sql=concat_ws(' ', @sql_prefix,
string_agg(string, N',') within group (order by itemno),
@sql_suffix)
from pvt_cte;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 25, 2022 at 6:24 pm
STRING_AGG is not available in SQL Server 2016.
Something like this maybe
with
unq_itemno_cte(itemno) as (
select distinct itemno
from #data),
pvt_cte(itemno, string) as (
select itemno, concat(@pvt_prefix,
itemno,
@pvt_middle,
itemno,
@pvt_suffix)
from unq_itemno_cte)
select @sql=concat(@sql_prefix,
stuff((select concat(', ', string)
from pvt_cte
order by itemno
for xml path('')),1,1,''),
@sql_suffix);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 25, 2022 at 6:34 pm
Again... if you post some sample data in a readily consumable format, we can help a whole lot more. See the first link in my signature line below for one of a couple different ways to do so.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2022 at 12:23 pm
Again... if you post some sample data in a readily consumable format, we can help a whole lot more. See the first link in my signature line below for one of a couple different ways to do so.
Here you go Jeff:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
businessday DATETIME,
ColumnName varchar(20),
ColumnView varchar(50),
ColToPivot varchar(20),
ColValue varchar(30)
)
--===== Insert the data
INSERT into #mytable
SELECT '2022-01-01', 'ABC', 'DIM_VIEW', 'BUSINESS_RISK', '2.35' UNION ALL
SELECT '2022-01-01', 'ABC', 'DIM_VIEW', 'MARKET CRED', '1.35 / $2.40' UNION ALL
SELECT '2022-01-01', 'ABC', 'DIM_VIEW', 'MONETAY FUND', '4.80' UNION ALL
SELECT '2022-01-01', 'ABC', 'APP_VIEW', 'BUSINESS_RISK', '0.95' UNION ALL
SELECT '2022-01-01', 'DEF', 'DIM_VIEW', 'BUSINESS_RISK', '1.15 / $0.90' UNION ALL
SELECT '2022-01-01', 'DEF', 'DIM_VIEW', 'MONETARY FUND', '71.70 / $122.47' UNION ALL
SELECT '2022-01-01', 'DEF', 'APP_VIEW', 'BUSINESS_RISK', '5.60' UNION ALL
SELECT '2022-01-01', 'GHK', 'APP_VIEW', 'BUSINESS_RISK', '3.85 / $10.08'
And here is the the result I need:
businessdayColumnNameColumnViewBUSINESS_RISKMARKET CREDMONETARY FUND
2022-01-01ABCDIM_VIEW2.351.35 / $2.404.80
2022-01-01ABCAPP_VIEW0.95nullnull
2022-01-01DEFDIM_VIEW1.15 / $0.90null71.70 / $122.47
2022-01-01DEFAPP_VIEW5.60nullnull
2022-01-01GHKAPP_VIEW3.85 / $10.08nullnull
The pivot columns can vary from week to week. Its for a weekly report. It will be for 7 business days at a time.
January 27, 2022 at 2:14 pm
Oops, I edited the code above and posted instead grr. Preemptively, imo concatenation is preferable to replacement when the strings are not complicated (such as this one here) because it "goes with the flow"
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply