-2

I'm posting the query below which I used to retrieve the data and output how it shows and how do I need also .. please let me know how can I convert rows to columns data

Santhosh kumar
  • 23
  • 2
  • 8
  • 1
    This is known as a pivot. It has been asked and answered hundreds and hundreds of times. Do you always have the same columns or are they dynamic? – Sean Lange Oct 03 '18 at 20:19
  • I have same Facility_MisFacID,data same many times – Santhosh kumar Oct 03 '18 at 20:20
  • Meaning the columns are always going to be the same? – Sean Lange Oct 03 '18 at 20:21
  • Yes sean its going to be same – Santhosh kumar Oct 03 '18 at 20:23
  • Are you working on this one @SeanLange – Santhosh kumar Oct 03 '18 at 20:28
  • @SeanLange i have somethink i like now Measure no of TotalPatients no of Soundpatients Lengt ofStay CLABSI abc1234563 Tcremes 7 MRSA abc8456957 Jyuiwn 9 MRSA abc8523694 Dsreram 17 CLABSI abc7456898 Tcremes 17 MRSA abc9852367 Jyuiwn 7 MRSA abc7874945 Rcrekiws 9 – Santhosh kumar Oct 04 '18 at 18:55
  • I have no idea what that means. If it is relevant to your question you should edit your question. Comments are horrible for formatting. – Sean Lange Oct 04 '18 at 19:00
  • its kind of similar only , but not related to above question . that's why I posted in link with exact screenshot where I have written everything on that screenshot – Santhosh kumar Oct 05 '18 at 14:42
  • you can go to this link to see my question,I'm limited to post questions today type in google https then :// and then followup with imgur and dot com/a/XbwlgJq – Santhosh kumar Oct 05 '18 at 14:43
  • In the above comment you can see my link @SeanLange..If i post whole link it will not let you see it so i formatted it to words – Santhosh kumar Oct 05 '18 at 14:45
  • If you have a new question you should start a new question here. – Sean Lange Oct 05 '18 at 14:55
  • @SeanLange I need to wait few days to ask question again here, Pleas help me sean – Santhosh kumar Oct 05 '18 at 15:14
  • Did you get a question ban or something? Sorry but I don't get paid for this and spending a bunch of time piecing together a question is not in the cards. – Sean Lange Oct 05 '18 at 15:16
  • Yes i have a question ban – Santhosh kumar Oct 05 '18 at 15:40
  • Make your next one better and you won't get banned. :) Post ddl and sample data. Make it easy for others to help. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Oct 05 '18 at 15:50
  • CAST(ISNULL(t.SSN,' ') AS CHAR(20)) + CAST(ISNULL(t.Number,' ') AS VARCHAR(20)) + – Santhosh kumar Oct 05 '18 at 16:24
  • @SeanLange How do i put comma after ssn in the above query .. im getting result as 123-45-6789001234 , but i need it to be as 123-45-6789,001234 – Santhosh kumar Oct 05 '18 at 16:25
  • Seriously....just stop. I am not going to spend a bunch of time unravelling data and requirements for a different question from comments. And for the sake of anything sane please don't ever store SSN in clear text. – Sean Lange Oct 05 '18 at 16:26

2 Answers2

0

you should use pivot when you want to convert rows to column base on one column 

    ( select left(BarCdmID,2) as BarCdmID ,
[0] as AME,
[1] as AMV, 
[2] as BHV ,
[3] as BRV,
[4] as EOR ,
[5] as IPA,
[6] as IPB,
[7] as LTC,
[8] as OHW from BARCDM_FACIL) T
    pivot 
(facility_misfacID
 FOR T.BarCdmID 
IN [0],[1],[2],[3],[4],[5],[6],[7],[8]
    ) as pvt
Ali Eshghi
  • 1,131
  • 1
  • 13
  • 30
-1

Just use conditional aggregation since the columns are constant.

select
    BarCdmID
    , AME = MAX(case when Facility_MisFacID = 'AME' then MyCount end) 
    , AMV = MAX(case when Facility_MisFacID = 'AMV' then MyCount end) 
    , BHV = MAX(case when Facility_MisFacID = 'BHV' then MyCount end) 
    , BRV = MAX(case when Facility_MisFacID = 'BRV' then MyCount end) 
    , EOR = MAX(case when Facility_MisFacID = 'EOR' then MyCount end) 
    , IPA = MAX(case when Facility_MisFacID = 'IPA' then MyCount end) 
    , IPB = MAX(case when Facility_MisFacID = 'IPB' then MyCount end) 
    , LTC = MAX(case when Facility_MisFacID = 'LTC' then MyCount end) 
    , OHW = MAX(case when Facility_MisFacID = 'OHW' then MyCount end) 
from
(
    Select BarCdmID = LEFT(BarCdmID, 2)
        , Facility_MisFacID
        , MyCount = count(*)
    from BarCdm_Facil
    group by LEFT(BarCdmID, 2)
        , Facility_MisFacID

) x
group by X.BarCdmID
order by BarCdmID
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • received the below error. Column 'X.BarCdmID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Santhosh kumar Oct 03 '18 at 20:39
  • 3
    I will fix that in a second but seriously if you can't find that I am concerned that you will just use this code and not understand what it is doing. – Sean Lange Oct 03 '18 at 20:42
  • Please help me with this one .. I have attached a screenshot how it looks in result set and how I need also link here ... https://imgur.com/a/XbwlgJq – Santhosh kumar Oct 05 '18 at 14:36