Today I needed to convert discrete TinyInt color values like

Column_name  | Type
-------------|--------
RedChannel   | tinyint
GreenChannel | tinyint
BlueChannel  | tinyint

to just an NVARCHAR(7) string like

Column_name  | Type
-------------|------------
HexColor     | NVARCHAR(7)

Here's how I did it:

SELECT
  RedChannel AS Red,
  GreenChannel AS Green,
  BlueChannel AS Blue,
  CONVERT(VARBINARY(2), RedChannel) AS RedBin,
  CONVERT(VARBINARY(2), GreenChannel) AS GreenBin,
  CONVERT(VARBINARY(2), BlueChannel) AS BlueBin,
  CONVERT(
    VARBINARY(3),
    RedChannel * 256 * 256 | GreenChannel * 256 | BlueChannel
  ) AS CombinedBin,
  '#' + CONVERT(
    VARCHAR(6),
    CONVERT(
      VARBINARY(3),
      RedChannel * 256 * 256 | GreenChannel * 256 | BlueChannel
    ),
    2 -- Using a style of '2' removes the '0x' at the beginning
  ) AS HexString
FROM
  Person

That will give an output like this:

Red Green Blue RedBin GreenBin BlueBin CombinedBin HexString
240 62    62   0xF0   0x3E     0x3E    0xF03E3E    #F03E3E
255 202   17   0xFF   0xCA     0x11    0xFFCA11    #FFCA11
172 190   214  0xAC   0xBE     0xD6    0xACBED6    #ACBED6
78  194   194  0x4E   0xC2     0xC2    0x4EC2C2    #4EC2C2