sql query help

Question

sql query help

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

sqlenthu 89358 wrote:

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.

Thanks. I see what I can do after work tonight.

As a bit of a sidebar, the use of TABS doesn't work worth a hoot in code windows. You can see everything is crammed together there. I changed the tab to spaces to get the alignment in the text-based desired example and used "Plain" for the code window type.

businessday ColumnName ColumnView BUSINESS_RISK MARKET CRED  MONETARY FUND
2022-01-01  ABC        DIM_VIEW   2.35          1.35 / $2.40 4.80
2022-01-01  ABC        APP_VIEW   0.95          null         null
2022-01-01  DEF        DIM_VIEW   1.15 / $0.90  null         71.70 / $122.47
2022-01-01  DEF        APP_VIEW   5.60          null         null
2022-01-01  GHK        APP_VIEW   3.85 / $10.08 null         null

A screen shot of a spreadsheet or SQL Server grid output would also work for future posts.

And, with that, I'll say that this isn't a "concatenation" requirement but the desired output made it look like it was. This is actually a "CROSSTAB" (or Pivot, which I don't use) requirement.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Use the test data you were kind enough to post (nicely done there), here's an answer that uses a CROSSTAB. If you actually DO want NULL to appear, just change the empty strings to NULLs.

 SELECT  BusinessDay, ColumnName, ColumnView
        ,[BUSINESS_RISK] = MAX(IIF(ColToPivot = 'BUSINESS_RISK',ColValue,''))
        ,[MARKET CRED]   = MAX(IIF(ColToPivot = 'MARKET CRED'  ,ColValue,''))
        ,[MONETARY FUND] = MAX(IIF(ColToPivot = 'MONETARY FUND',ColValue,''))
   FROM #MyTable
  GROUP BY BusinessDay, ColumnName, ColumnView
  ORDER BY BusinessDay, ColumnName, ColumnView DESC
;

If it really does need to be dynamic, post back. That's almost as easy and there will be no cursor involved but I think you can figure out how to do it now by comparing the code above with the technique that other's posted about in my 2nd article on the subject. Here's the link to that, again, just to make it easy.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 3

Jeff Moden wrote:

Use the test data you were kind enough to post (nicely done there), here's an answer that uses a CROSSTAB. If you actually DO want NULL to appear, just change the empty strings to NULLs.

 SELECT  BusinessDay, ColumnName, ColumnView
        ,[BUSINESS_RISK] = MAX(IIF(ColToPivot = 'BUSINESS_RISK',ColValue,''))
        ,[MARKET CRED]   = MAX(IIF(ColToPivot = 'MARKET CRED'  ,ColValue,''))
        ,[MONETARY FUND] = MAX(IIF(ColToPivot = 'MONETARY FUND',ColValue,''))
   FROM #MyTable
  GROUP BY BusinessDay, ColumnName, ColumnView
  ORDER BY BusinessDay, ColumnName, ColumnView DESC
;

If it really does need to be dynamic, post back. That's almost as easy and there will be no cursor involved but I think you can figure out how to do it now by comparing the code above with the technique that other's posted about in my 2nd article on the subject. Here's the link to that, again, just to make it easy.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs%5B/quote%5D

Yes Sir. I am looking for a dynamic query. This weekly report might have different list and/or numbers of ColToPivot so need the query to handle that case.

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 4

I was out because of Covid. Could you please help me with the dynamic query ?